Skip to Content
Author's profile photo Former Member

Maximum of a Dimension Values – Dates

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.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Gopinath Kolli
      Gopinath Kolli

      Nice blog 🙂

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thank You Gopi 🙂

      Author's profile photo Thangaraj Selvaraj
      Thangaraj Selvaraj

      Informative one Sujith.

      Author's profile photo Mustafa Bensan
      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:


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


      The script code is as follows:

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

      var len = myDates.length;

      var lastDate = myDates[len-1];


      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.



      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Mustafa,

      Thanks for the valuable information. 🙂

      I have changed the content accordingly.



      Author's profile photo Alfonso Hernandez
      Alfonso Hernandez


      I used the same script, but the script returns as a result the last month in the master data. Do you know how can I restrict only posted data?

      I tried to restrict the result in the Bex Query suppressing the rows without values and forced the Data Source only display values with posted data.

      When I edit the Data Source the information is displayed correctly.

      But when I run the dashboard locally or in Launchpad, it continues taking the values of the master data (APPLICATION_ALERT)