when I use the CumVal in the chart it is not giving me the right value - I have to get the cumulative value considering the above date, sector, region filter values - if I select Jan 2018 then the sum or cumval should be from Jan2017 to Jan2018
what this value means is so far or as of Jan 2018 i have these many LicensedUsers; if I select Sector I have these many users in this sector and the same for region
Could anyone please help where I missed - attached the sample data for your understanding - thanks a lot for your time
Create a new table from the data source and drop the CUMU field.
Order your table by Sector - Region - Date
Create a new field for each Section Region combination. Let's call it LicCum.
On the first row of a Section-Region combination, LicCum will contain the value of your LIC column
On the second row (e.g. next month) of that same Sector-Region combination, LicCum will contain the current LIC value added to the previous LicCum value (use peek() to read from the table you are assembling)
And so on.
Whenever Sector-Region changes, reset LicCum to the current value of LIC.
In your document, create an object and use Sum(LicCum) to aggregate all YTD values for all Sector-Region selections. Only a single month seleciton should be allowed. Use Max() in Set Analysis to filter the most recent month from whatever the user has chosen in your calendar.
Hi Peter, I think I have not explained it properly;
I have a detail dataset from which I prepare a date column ML_Data_AsOf which includes the values like 1/1/2017, 2/1/2017 etc
and the other columns - then I create a link table with the distinct values of ML_Data_AsOf and this is joined to the calendar table which again includes values like 1/1/2017,2/1/2017 etc - the link table is joined to the calendar table and the detail table
at the script level I am trying to calculate the cumu value so that it will be shown when the filters are selected like year, month
ML_QtyLic - is the column that includes values, I need to have cumulative column - thanks a lot for your time
This is how my tables look like - The filters are from the Link Table
In my table to the left I have a numeric field ML_QtyLic which includes the numeric values like 1,0 - I am trying to create another column which includes the cumulative values - I have attached sample data in the excel file- thanks a lot for your time