I have been looking at many posts in Design Studio, where users are asking to get the maximum value of a date dimension and restrict data in the dashboard for that latest date or month or year from the database.

This is very useful in case of inventory dashboards where the inventory value or count is viewed for only last day or month or may be year (seldom viewed).

I had to come across a similar requirement in my project in case of inventory where client wants to see only the latest month inventory.

Since the month or date or year is dimension and cannot use aggregation of “Max”, we need to manually write a script to find out the latest month available in the data and restrict the entire dashboard to that month.

There is a simple way to do this.

First have a data source with the date or month or year dimension for which the max value needs to extracted.

Lets say in my example i want to find latest month, and from the DB i was getting this month in the format “YYYYMM”.

I have created a DS with name DS_1 with dimension “Month” and some measure as its mandatory to have one measure in the DS initial view.

Now go the application properties and create a global variable v_LatestMonth as String.

Now go the onStartup events, and write the below code to fetch the latest month.

var months = DS_1.getMembers(“Month”,<no of months you want to fetch>); //local array months to hold the values from the DS. use a number where all the values are fetched.

var maxValue = 0; // to hold maximum value

months.forEach(function(element, index) {

  if(maxValue < Convert.stringtoInt(element.text)) {

          maxValue = Convert.stringtoInt(element.text);

     }

});

v_LatestMonth = Convert.floattoString(maxValue);


Now  the v_LatestValue will hold the maximum month value.

This can be used in and getData functions or setFilter functions to restrict for the latest month.

To report this post you need to login first.

5 Comments

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

  1. Mustafa Bensan

    Hi Sujith,

    By way of clarification I thought I’d mention that the topic of getMembers() sort order is one that has been discussed before on the SCN and you have to be careful about making assumptions in this context.  The sort order selection in the Initial View actually has no effect on the sort order of getMembers().  In the “Month” example, it is just a coincidence if you end up with the latest month.  This is easy to verify.  If in your example, instead of sorting the month in ascending order you select descending order in the Initial View, you’ll find that you will still retrieve the same month as before and not the minimum month for example.  The sort order is related to the sort in the backend and not the sort specified in the Initial View.

    Here’s an example with a BEx Query as a data source:

    Initial View with Calendar Day sorted in DESCENDING ORDER:

    MemberSortInitialView.png

    Verification of sort order in Crosstab and result of getMembers():

    MemberSort.png

    The script code is as follows:

    var myDates = DS_1.getMembers(“0CALDAY”, 100);

    var len = myDates.length;

    var lastDate = myDates[len-1];

    TEXT_1.setText(lastDate.externalKey);

    Even though the Calendar Day has been sorted in DESCENDING ORDER in the Initial View, the script still returns the maximum date because this is the order in the backend.  i.e. the sort order of the initial view is not respected by getMembers().  If you happen to get a result that appears to correspond to the initial view sort order then that is just a coincidence.

    Regards,

    Mustafa.

    (0) 

Leave a Reply