Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

RangeSum set analysis in Pivot Table

Hi ,

I have a problem here regarding RangeSum set analysis in Pivot Table.

Below table is the output i want  and the input is the first 4 columns.  The final column is the accumalation of the STO needs on product level.

I want to use set analysis to achieve this output. So i used RangeSum function.

When i only add "Product "and "STO _NO" dimensions in the pivot table, the analysis i wrote "rangeSum(Sum(STO_Needs),Above(Accumalation))" works as i expected. But when i added the third dimension "DC_NO", this set analysis didn't work.

Could someone help here?

Attached is the QVW file you can work on.

 
ProductSTO_NoDC_NoSTO NeedsAccumalation
00169560001DCGACN88
00169560002DCGACN816
00169560003DCGACN824
00169560004DCGACN832
00169560005DCGACN840
00169560006DCGACN848
00169560007DCGACN1664
00169560008DCGACN872
00169560009DCGACN880
00169560010DCGACN888
00169560011DCGACN896
00169560012DCGACN8104
00169560013DCGACN8112
00169560014DCGACN8120
00169560015DCGACN16136
00169560016DCGACN16152
00169560017DCGACN8160
00169560018DCGACN8168
00169560019DCGACN8176
00169560020DCGACN8184
00169560021DCGACN16200
00169560022DCGACN16216
00169560023DCGACN8224
00169560024DCGACN8232
00169560025DCGACN8240
00169560026DCGACN8248
00169560027DCGACN16264
00169560028DCGACN24288
30234229001DCGAPAU1010
30234229002DCGAPAU1020
30234229003DCGAPAU1030
30234229004DCGAPAU1040
30234229005DCGAPAU1050
30234229006DCGAPAU1060
30234229007DCGAPAU1070
30234229008DCGAPAU1080
30234229009DCGAPAU1090
30234229010DCGAPAU10100
60185269027DCGACN1616
60185269028DCGACN2440
 
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Try like:

RangeSum([STO Needs], If(Product=Above(Total Product), Above(total Accumalation),0))

View solution in original post

7 Replies
Highlighted
MVP
MVP

Try like:

RangeSum([STO Needs], If(Product=Above(Total Product), Above(total Accumalation),0))

View solution in original post

Highlighted
Contributor II
Contributor II

Hi Treseco,

Really thanks for your help.

It works and also give me the inspiration. Super thanks.

Highlighted
Creator II
Creator II

Hi Nice solution, but can you explain why this will work? Thanks!

Highlighted
MVP
MVP

Hi Alex,

Your question sounds(to me) in two possible ways:

 - You think that this might not work (if so, please let me know why. I would give another try to fix that)

 - You might not have understood some of the section of the expression (if so, please let me know exactly which section you need clarification with).

 

Thanks. 

Highlighted
Creator II
Creator II

Hi thanks for the reply, it's the latter, the expression you post contains this: If(Product=Above(Total Product)

which was added to let the formula work, I don't understand why we need to add this part to let it work. Thanks!

Highlighted
MVP
MVP

Without If(Product=Above(Total Product),you would notice that the accumulation would happen across Product dimension, i.e. - irrespective of product the accumulation goes adding up start to end. However, with this code we are checking if there is a change in product value in dimension. If there is a new product then we are resetting the accumulation with zero reference to the earlier value.

 

Hope this helps.

Highlighted
Creator II
Creator II

Thanks, you are awesome!! Happy New Year!