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.
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.