Solved: Acumulative sum where you have so many dimensions - Qlik Community

Anonymous · ‎2017-09-25

I'm trying to get value like the attached QVW in a pivot table. But my data is very large and I do have many dimensions (more than 15)

How do you think the best practice to achieve this?

sunny_talwar · ‎2017-09-28

You can use set analysis in the Avg(1) field to restrict to your date range

RangeSum(Before( Count({<FLAG={'INV'},AssetStatusID={'REP','INV'},DATEPERIOD=, Year, Month}>} AssetModelKey), 0, ColumnNo())) * Avg({<DATEPERIOD = {">=$(=Date(vDateFrom2, 'M/D/YYYY'))<=$(=Date(vDateTo2, 'M/D/YYYY'))"}>} 1)

View solution in original post

Anil_Babu_Samineni · ‎2017-09-25

I believe what you done is optimized one only because in model it storing and creating only one table that mean we are not creating Link table and management / business logic tables ...

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Anonymous · ‎2017-09-25

No. I am trying to create one. But when I use the same method like the QVW I attached, the script's load is taking so much time (more than 30 mins).

In that QVW there are only 2 dimension, so I probably can use this on the script:

if(RowNo()=1 or Region&SubRegion<>previous(Region&SubRegion),Value,Value+peek(CumVal)) as CumVal

What if I have so many dimensions?

Anil_Babu_Samineni · ‎2017-09-25

Even thought, If you have more than 10 fields it may useful

Load *, AutoNumber(Region&SubRegion&...&...&....) as Key

Then this?

if(RowNo()=1 or Key<>previous(Key),Value,Value+peek('CumVal')) as CumVal

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

sunny_talwar · ‎2017-09-25

Is this what you are looking to get?

Expression

RangeSum(Before(SUM(DISTINCT {<Period>} Value), 0, ColumnNo())) * Avg(1)

Anonymous · ‎2017-09-27

hi

may i ask why you use *avg(1) in the expression? thanks

sunny_talwar · ‎2017-09-27

I have used {<Period>} in set analysis which will ignore selection in Period and will show all periods even if you select 1 or multiple of them.... Avg(1) will equal to 1 for the selected period and will equal to 0 for periods which you have no selected forcing the expression to be 0 for non selected period... This is basically an alternative to writing an if statement like this

If(Period, RangeSum(Before(SUM(DISTINCT {<Period>} Value), 0, ColumnNo())))

Anonymous · ‎2017-09-27

I used that expression to do so in my real data.

If I select all the period where the data exist, it will give me the correct values.

But when I select only one or some period (not all), the cummulative sum went wrong.

Here is if I select all period where data exist:

If I select only Jan, the cumulative must be 5

Expression i use:

RangeSum(Before( Count({<FLAG={'INV'},AssetStatusID={'REP','INV'},DATEPERIOD=}>} AssetModelKey), 0, ColumnNo())) * Avg(1)

sunny_talwar · ‎2017-09-27

Try this

RangeSum(Before( Count({<FLAG={'INV'},AssetStatusID={'REP','INV'},DATEPERIOD=, Year, Month}>} AssetModelKey), 0, ColumnNo())) * Avg(1)

I added Year and Month in set analysis because you were making selection there... for this to work, you will need to add all your date and time selection field in your set analysis

Anonymous · ‎2017-09-28

It works! Thanks

But I have 1 more problem, when I only select the acumulative value and use period from to (Calendar Object) I get the result wrong. All the date period is showed, even though i didnt choose it

Date from: min value: min(DATEPERIOD), Max value = max(DATEPERIOD)

Date TO: min value: vDateFrom2, Max value =max(DATEPERIOD)