Skip to Content

One classic requirement while developing dashboards is to visualize a particular KPI trend. In most cases, the trend analysis view would be depicted as a Trailing Period from the current month or Quarter or day.  At times, there are scenarios where customer wants an option to change the N in Trailing N months/quarters/days at run time (N being the number of months).


In this blog, I will explain my particular scenario and how I implemented a solution to change the Trailing N months (this concept can also be used to calculate for following N period such as year, quarter, month, date)


Scenario

The requirement was to display a chart with Trailing 12 Months of data from the current month and the end user should have an option to change the Trailing months to 6,12,18,24 etc., during run time.


Trailing months.png



How this is generally done

This scenario can be solved in various ways. Here are a couple of ways that is often used:

Option 1 – Using Multiple Queries

To show Trailing 6 Months of data, you have to create a query in SAP Business Explorer with Month dimension restricted to a current month variable.

This variable should be defined as a range [currentmonth variable -6 to current month variable].

Since the offset specified for a variable cannot be changed dynamically at run time, you would end up using multiple Queries for every trailing N Months, which in turn will increase the load on the dashboard.

Option 2 – Create One Query with two variables

Create a query in SAP Business Explorer and restrict the month dimension with two variables: 1.Period_From and 2.CurrentMonth Variable

Calculate the Nth month in SAP BusinessObjects Desgin Studio using an if-else logic and pass the value of the Nth month to Period_From Variable .

Although this method uses only one Query, it triggers the variable for every selection made in the radio button for Trailing N month Selection. This increases the render time of the charts used in the dashboard.


How this can effectively be done using Arrays in SAP BusinessObjects Design Studio

In my dashboard, I adopted an approach that uses the Arrays in SAP BusinessObjects Design Studio to effectively change the numbers of trailing months to be displayed. Here is what I did:

Using One Query without any variables

First Step was to determine the current month from SAP BusinessObjects Design Studio using the following code –

  1. APPLICATION.getInfo().dateNowInternalFormat.substring(0,6);

The output for this script was the current month displayed in the format “YYYYMM” – “201505”.

In my dashboard, I have a column chart that has an SAP Business Explorer query as the data source assigned to it. Above this chart, I used a radio button group to provide users an option to choose the number of Trailing Months. I added the number items to the radio button in SAP BusinessObjects Design Studio directly as shown below:


/wp-content/uploads/2015/10/radiobutton_812965.png


In the Onselect() event of the radio button, I used the following script:


/wp-content/uploads/2015/10/radiobutton_script_813002.png


/*Array_1 to find the index of the current month*/

var montharray=DS_1.getMembers(“0CALMONTH”, 10000);

var find=””;

  1. montharray.forEach(function(element, index) {

if(element.internalKey===current_month)

{

            find=index+””;

}

});

/*Calculate the difference between the index and the N value*/

var from=””;

var indexdifference=Convert.stringToInt(find)-Convert.stringToInt(RADIOBUTTONGROUP_1.getSelectedValue());

/*Array_2 to find the trailing Nth month */

  1. montharray.forEach(function(element, index) {

if(index==indexdifference){

            from=element.internalKey;

}

});

DS_1.setFilter(“0CALMONTH”, {“low”:from,”high”:current_month});

Keep reading for a more logical explanation of the script.

Logic

  Step 1 – In the script, I used an array – Array_1  to find the index of Current Month


Step1.png

Step 2 – I found difference between the index and the N value selected. Then I used the result as a new index.

Step 3 – Using another array – Array_2, I obtained the Trailing Nth month using the new index.



/wp-content/uploads/2015/10/step3_813004.png

After obtaining the two months, I used the setFilter API to filter the data for the selected value range.

DS_1.setFilter(“0CALMONTH”, {“low”:from,”high”:current_month});

Output

On executing the application, I was able to change the Trailing N months by clicking on the desired radio button during runtime. The output for my selection of ’18 Months’ is shown below:


/wp-content/uploads/2015/10/output_813005.png


Conclusion

I believe this method is more effective for any dashboard design as it reduces the number of queries to one and also eliminates the Query interactions for every selection made to change the N value. You can also try the same logic to achieve the same results for following N Months / Quarters / Days. I really hope this blog was useful for you!

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. vijaykumar ijeri

    Hi Karthik,

    Really good one. Appreciate your time in writing down and explaining.

    Elements should be sorted before and then indexed i guess right?

    You have handled the sorting part in bex?

     

    Thanks & Regards,

    Vijay

    (0) 
    1. Karthik Swaminathan Post author

      Thanks Vijay.

      Yes, the members must be sorted in the desired order before using this technique (it should be handled in BEx).

      In this case I haven’t done sorting since the Calmonth dimension is sorted in ascending by default .

       

      Thanks

      Karthik S

      (0) 
  2. Archana S

    Hi Karthik,

     

    Thanks for your blog

     

    I tried referring your link for one of my requirement which is similar to this. I need to restrict the no of months to latest 3 months (current month ,previous month, and its previous month),which should change dynamically ,for  the first time login.

    I am consuming the view directly via OLAP in Design studio.(from SAP HANA)

     

    The format of month start date in my end is YYYY-MM-DD.

     

    Can you please help me in this?

     

     

    Regards

    Archana

    (0) 

Leave a Reply