
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Product | STO_No | DC_No | STO Needs | Accumalation |
00169560 | 001 | DCGACN | 8 | 8 |
00169560 | 002 | DCGACN | 8 | 16 |
00169560 | 003 | DCGACN | 8 | 24 |
00169560 | 004 | DCGACN | 8 | 32 |
00169560 | 005 | DCGACN | 8 | 40 |
00169560 | 006 | DCGACN | 8 | 48 |
00169560 | 007 | DCGACN | 16 | 64 |
00169560 | 008 | DCGACN | 8 | 72 |
00169560 | 009 | DCGACN | 8 | 80 |
00169560 | 010 | DCGACN | 8 | 88 |
00169560 | 011 | DCGACN | 8 | 96 |
00169560 | 012 | DCGACN | 8 | 104 |
00169560 | 013 | DCGACN | 8 | 112 |
00169560 | 014 | DCGACN | 8 | 120 |
00169560 | 015 | DCGACN | 16 | 136 |
00169560 | 016 | DCGACN | 16 | 152 |
00169560 | 017 | DCGACN | 8 | 160 |
00169560 | 018 | DCGACN | 8 | 168 |
00169560 | 019 | DCGACN | 8 | 176 |
00169560 | 020 | DCGACN | 8 | 184 |
00169560 | 021 | DCGACN | 16 | 200 |
00169560 | 022 | DCGACN | 16 | 216 |
00169560 | 023 | DCGACN | 8 | 224 |
00169560 | 024 | DCGACN | 8 | 232 |
00169560 | 025 | DCGACN | 8 | 240 |
00169560 | 026 | DCGACN | 8 | 248 |
00169560 | 027 | DCGACN | 16 | 264 |
00169560 | 028 | DCGACN | 24 | 288 |
30234229 | 001 | DCGAPAU | 10 | 10 |
30234229 | 002 | DCGAPAU | 10 | 20 |
30234229 | 003 | DCGAPAU | 10 | 30 |
30234229 | 004 | DCGAPAU | 10 | 40 |
30234229 | 005 | DCGAPAU | 10 | 50 |
30234229 | 006 | DCGAPAU | 10 | 60 |
30234229 | 007 | DCGAPAU | 10 | 70 |
30234229 | 008 | DCGAPAU | 10 | 80 |
30234229 | 009 | DCGAPAU | 10 | 90 |
30234229 | 010 | DCGAPAU | 10 | 100 |
60185269 | 027 | DCGACN | 16 | 16 |
60185269 | 028 | DCGACN | 24 | 40 |
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try like:
RangeSum([STO Needs], If(Product=Above(Total Product), Above(total Accumalation),0))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try like:
RangeSum([STO Needs], If(Product=Above(Total Product), Above(total Accumalation),0))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Treseco,
Really thanks for your help.
It works and also give me the inspiration. Super thanks.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Nice solution, but can you explain why this will work? Thanks!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks, you are awesome!! Happy New Year!
