Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

qvraj123
Contributor II

cumulative within the load script - need help

Hi All,

I have a daset in which I have a date field which includes the values like 1/1/2017,2/1/2017,3/1/2017,4/1/2017 etc

these are the ones that I use in the graph for showing the month and these are based on the transactional date column. I also

have other detail columns in the dataset. I have another 2 fields called Region and Sector which I will be using as filters and the

metric that is shown is the sum of a field called  - ML_QtyLic , I need to get the cumulative value for this

1). If I select March 2018 in the filter it should give me the sum of all the MI_QtyLic - I.e starting from 2017 to March 2018

   if I select Feb 2018 then the sum should be for Jan 2017 (this is my starting year) to feb 2018

2). the cumulative values should also be for sector and region if i select a value from these filters

I tried to get the data sorted then using the Peek function but it is not giving me the correct value -

IF(RowNO()=1,
ML_QtyLic,
if(ML_Data_AsOf=Peek(ML_Data_AsOf),
ML_QtyLic + Peek(Cumuval),
ML_QtyLic
)
) AS Cumuval

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

5 Replies
qvraj123
Contributor II

Re: cumulative within the load script - need help

Hi All,

if I have  a dataset in the following format - I need to add a cumulative column in the load script itself so that

when I apply the filter on year, month, region, sector I will be able to see the cummu value

DATE  SECTOR  REGION  LIC CUMU

1/1/2017 AAA  ASIA  1 1

1/1/2017 AAA  EMEA  1 2

1/1/2017 AAA  LATAM  1 3

1/1/2017 AAA  NAM  1 4

1/1/2017 BBB  ASIA  1 5

1/1/2017 BBB  EMEA  1 6

1/1/2017 BBB  LATAM  1 7

1/1/2017 BBB  NAM  1 8

2/1/2017 AAA  ASIA  1 9

2/1/2017 AAA  EMEA  1 10

2/1/2017 AAA  LATAM  1 11

2/1/2017 AAA  NAM  1 12

2/1/2017 BBB  ASIA  1 13

2/1/2017 BBB  EMEA  1 14

2/1/2017 BBB  LATAM  1 15

2/1/2017 BBB  NAM  1 16

My filters will be Year, Month, Sector, Region - if I select FEB 2017 I should see the cumu value in the graph

When I use the "Full Accumulation" in the graph it will not show the cumu value from the beginning as it is only showing the value for the selected month -

how do we get the - BELOW value so that I can add it to the first record

thanks a lot

Highlighted

Re: cumulative within the load script - need help

In your load script

  • 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.

Re: cumulative within the load script - need help

BTW Below() is a chart function, not a script function.

qvraj123
Contributor II

Re: cumulative within the load script - need help

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

qvraj123
Contributor II

Re: cumulative within the load script - need help

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