Qlik Sense RangeSum Over Multiple Dimensions

[JandreKillianRIC]

Hi All 

This is my data load (You cant change anything here) 

Sales:
Load * Inline [
CardCode,ItemCode,Quantity,Date
2,7,2,45057
2,2,3,45657
3,2,87,45061
2,6,52,45413
2,6,4,45298
4,9,11,45281
2,2,68,45487
3,8,47,45023
4,3,13,45399
1,9,73,45074
3,7,74,45373
3,3,47,45093
1,7,76,45428
4,4,64,45114
2,3,49,45313
3,1,15,45067
2,7,54,45013
3,5,72,45468
4,3,1,45193
2,3,51,45422
1,10,5,45103
3,5,52,45293
1,2,9,45115
3,2,43,45492
4,2,53,45355
4,2,51,45606
3,10,7,45306
2,4,47,45352
4,8,91,45328
2,4,87,45640
4,3,89,45649
4,8,43,45317
4,8,71,45651
1,1,38,45434
3,3,38,45316
4,4,69,45010
1,5,75,45532
3,2,74,45327
3,9,28,45143
2,6,57,45252
1,9,63,45151
2,4,65,45193
1,1,50,45653
1,8,18,45275
3,10,87,45006
4,1,7,45461
1,9,72,45107
4,1,30,45479
2,10,92,45540
3,7,100,45256
1,6,57,45092
3,2,84,45227
4,5,29,45528
2,4,30,45273
2,10,7,45231
4,6,10,45515
2,9,21,45379
1,3,37,45124
1,10,61,45596
1,7,72,45168
3,9,85,45505
3,4,73,45118
3,10,77,45524
1,1,2,45291
3,9,55,45633
2,1,58,45643
1,4,6,45331
3,8,78,45414
3,3,85,45607
1,6,40,45568
4,8,25,45226
4,8,90,45457
1,2,15,45002
4,7,70,45330
2,8,66,45457
3,7,98,45635
4,8,86,45122
1,7,31,45426
3,8,77,45385
1,5,79,45497
4,3,13,45418
2,10,41,45597
1,2,78,45442
4,6,88,45043
1,7,62,45347
2,5,6,45676
1,10,68,45551
3,6,47,45520
3,6,28,45248
3,2,14,45451
1,8,90,45568
3,5,52,45406
3,7,34,45106
2,3,63,45558
3,1,64,45268
3,8,30,45273
1,2,11,45414
1,2,40,45321
1,8,99,45409
];



CardName: 
Load * Inline [
CardCode,CardName
1,Hospital 1
2,Hospital 2
3,Hospital 3
4,Hospital 4
];



ItemName: 
Load * Inline [
ItemCode,ItemName
1,Item 1
2,Item 2
3,Item 3
4,Item 4
5,Item 5
6,Item 6
7,Item 7
8,Item 8
9,Item 9
10,Item 10
];

Build a table with Item Name as Dimension, Sum(Quantity) as Measure

Now:
Issue 1 - Add a measure - RowNo()
When adding RowNo() as a Measure - You cant sort anymore. (this is understandable) 

Notable Mention 
When adding a rangesum to the same table 
Measure = RangeSum(Above(Sum(Quantity), 0, Rowno())) this works.

Now lets add dimensions, add CardName to the table 
You will see this still works but sorting has already been (Disabled in step 1 so the data just looks weird) - You can sort on CardName at Item but not Quantity.

Now we will add the sort by expression on the dimensions to try and fix this. 
Order is Sum(Quantity), then CardName then Item Name I sort by expression =Sum(Quantity) however - I am still not getting the sorting to match - See below - We need to get the highest Quantity per Cardname per Item (Desc Order)

Now - add 2 more Dimensions, CardCode and ItemCode - Now - the RangeSum doesn't work anymore

We can add the total parameter, using this RangeSum(Above(Total Sum(Quantity), 0, Rowno(Total))) which will fix the rolling sum, however this is now across all the items, CardName.

To fix this we need to get the RowNo() Parameter to work (in the correct order) inside the above function. Add this Aggr(RowNo(), CardCode, ItemCode) as a measure. Now this is wrong due to the load order in the aggr. 
See below (The Hospital 1 is not correct in this aggr - Due to Load Order)

Now we need to use a structured Aggr... That passes a sorting. This is the part were I am not getting the desired result. 
This is my aggr = Aggr(RowNo(), CardCode, ItemCode, (ItemCode, (=Sum(Quantity), Descending)))

According to me... As soon as I get the Aggr (Using the structured parameter to work) the RangeSum result should look something like this 
RangeSum(Above(Total Sum(Quantity), 0, Aggr(RowNo(), CardCode, ItemCode, (ItemCode, (=Sum(Quantity), Descending)))))

However this is not working. This is my desired result in Qlik - Per CardName, Highest to lowest RangeSum based on Quantity. 

Thanks in advance

[marcus_sommer · Accepted solution]

You may try it with a calculated dimension for the items, maybe something like:

aggr(dual(ItemCode, sum(Quantity)), CardCode, ItemCode)

[JandreKillianRIC]

Hi @marcus_sommer 

I changed all my dimensions to Calculated ones (Like the above one you suggested). Changed the sorting to Expression = Sum(Quantity) 

This definitely assisted in the sorting of the table! Thanks a mil! However I still cant get my RangeSum to sum only per CardName. Please see my expected result from the post, Any chance you can assist with that? Here is what I have now.

Regards Jandre

[JandreKillianRIC]

Hi @marcus_sommer 
I got it sorted using this for my RangeSum. 

RangeSum(Above(Total Sum(Quantity), 0, Aggr(Rank(Sum(Quantity), 4, 1), CardCode, ItemCode))) 

But do you think this is the best solution? Or do you know of another way that I can accomplish this? 

Regards Jandre