Skip to Content
avatar image
Former Member

Calculate Rolling 12 Months to Display in Chart from a HANA View

Folks, i have a situation where i need to display rolling 12 months into a chart where the data source is a HANA view.

Following are the columns i have in my HANA View.

Month Month_text CM PM

1/2014 Jan-14 100 98

---------------------------------------------------

7/2017 Jul-17 80 71

This is my initial view. I do not have other columns in this View. This is a very basic View without too many columns.

Now i want to display data from Aug-16 to July-17. There is no Input parameter on the View which can display rolling 12 months for me based on month value i pass.

I am having trouble to figure out the command that could help me in solving what i need.

I have gone through few blogs from other developers here but i am unable to find an answer.

https://blogs.sap.com/2015/10/19/how-to-use-arrays-in-sap-businessobjects-designstudio-for-trailing-and-following-n-months/

In the blog above the writer used the Index column calculate the rolling 12 months. But in my case i do not have a provision like. All i have is Month and Month_text.

So, my filter command should look something like this.

DS_1.setFilter("Month",[8/2016,9/2016,10/2016,11/2016,12/2016,1/2017,2/2017,3/2017,4/2017,5/2017,6/2017,7/2017])

I am aware i can achieve current date and month from Application.getInfo().datenowInternalFormat and take the string and bring out month and year and concatenate them to get 7/2017. But calculating the other months is big difficult.

Any help would be greatly appreciated.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Aug 05, 2017 at 12:09 AM

    Hi Rajasekhar,

    You can try applying the following code. Make sure the startMonthYear and endMonthYear variable calculations are adapted as needed to format according to the INTERNAL format of the "Month" dimension.

    var currentDate = APPLICATION.getInfo().dateNowInternalFormat;
    var endYearNum  = Convert.stringToInt(currentDate.substring(0,4));
    var endMonthNum = Convert.stringToInt(currentDate.substring(4,6));
    var endYearMonth = currentDate.substring(0,6);
    var startYearNum = 0;
    var startMonthNum = 0;
    
    if (endMonthNum == 12) {
    
    	startYearNum = endYearNum;
    	startMonthNum = 1;
    	
    } else {
    
    	startYearNum = endYearNum - 1;
    	startMonthNum = endMonthNum + 1;
    
    }
    
    var startMonthYear = startMonthNum + "/" + startYearNum; // Adapt to appropriate internal key format
    var endMonthYear = endMonthNum + "/" + endYearNum;       // Adapt to appropriate internal key format
    
    DS_1.setFilter(“Month”, {“low”:startMonthYear,”high”:endMonthYear});

    Regards,

    Mustafa.

    Add comment
    10|10000 characters needed characters exceeded