Skip to Content
0
Former Member
Jun 13, 2016 at 09:20 PM

Creating a Current Year YTD Filter, Previous Year YTD, CY MTD, and PY MTD filters

603 Views

Hi folks, i need to create Current Year YTD, MTD and Previous Year YTD, MTD filters in Universe where the backend is HANA. Being new to HANA i am unable to figure out the logic for it. I have a FIS_YEAR, CAL_YEAR_MONTH, FIS_MONTH fields in the existing HANA View.

Need to create filters in Universe level so i can create a dashboard on top of it. I know we have the corresponding Last Year formula in WebI but i am not creating a report here so need to limit the data in query level itself.

I referred to certain blogs in here and came up with a code

extract(year from "_SYS_BIC"."FIS_YEAR") = extract(year from current_date) but this filter is bringing back 2015-07 to 2016-06 results. But i need only from 2016-01 to 2016-06 values.

Need another filter to bring 2015-01 to 2015-06 values.

i edited the above code to try bringing in MTD data with below code

extract(month from "_SYS_BIC"."CAL_YEAR_MONTH") = extract(month from current_date) but this filter is bring back 2012-06, 2013-06, 2014-06, 2015-06, 2016-06 values.


Filter on Current and Previous Year in IDT | SCN

This was the blog i referred and tried to create my filter. Any help in this regards would be helpful. At this stage i do not have the flexibility to ask for changes in HANA view. So need to figure out the logic in Universe itself.