How to perform dynamic YTD and LYTD in Design Studio
Reporting Tool: Design Studio
Business scenario:
User wants to display year to date(YTD) and last year to date(LYTD) data dynamically based on current /system date in the based on any dimension instead of hardcoding the years of date in initial view. Here we considered country as dimension wise .
In general we hard code filter members in data source as below.
Reporting solution:
We can write a java script to dynamically populate YTD and LYTD based on system date in real time scenario which will be helpful for comparing the spend from current year to last year.
Back End: HANA Studio
One of the calculation view is sourced to design studio from HANA connection.
Bind the data source to chart to display YTD and LYTD data country wise.
create a data source from HANA studio and set initial view as below.
The below piece of code is written on start up of the application.
The function datenowInternalFormat() gives current date of system .
The 1st four digits of the date is year which should be considered to give Year to date data and last year to date data. In general ,real time data flows regularly into database so below piece of code gives YTD ,LYTD.
The test system has data only till 2014 .so I have given offset of -3 and -4 to generate output based on data available in database i.e (YTD:2014,LYTD :2013).
System generated year will be 2017 for current date. So I have set below offsets to generate 2014 and 2013 data.
Example:
Application system date is found out using : datenowInternalFormat()
1) find system date
2)Split DD,MM,YYYY from YYYY-MM-DD and store it in local variables
3)Here we store system date in variable ‘a’ . now from variable ‘a’ we fetch year which is used to derive variable ‘b’ is current year and variable ‘c’ is last year.
4)Filter it in your data source accordingly as below
Output:
Below screenshot shows data for various countries with 2014 and 2013 data.
The offsets
The offsets can be set based on user requirement for historical data requested.
The above graph shows global spend for various countries 2014 and 2013 wherever exists.