Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a pivot table and I try to obtain a progressive value on Total rows as explained in the image.
I attached a *.qvw
Those are a simple data values.
T:
LOAD * INLINE [
Year, Month, Group, Value
2017, 1, A, 1
2017, 1, B, 2
2017, 1, C, 3
2017, 1, D, 4
2017, 2, A, 5
2017, 2, B, 6
2017, 2, C, 7
2017, 2, D, 8
2017, 3, A, 9
2017, 3, B, 10
2017, 3, C, 11
2017, 3, D, 12
2017, 4, A, 13
2017, 4, B, 14
2017, 4, C, 15
2017, 4, D, 16
];
I hope someone can help me.
Best Regards
Luca Jonathan Panetta
This:
If(Dimensionality() <> 2, Sum(Value),
Aggr(RangeSum(Above(TOTAL Sum(Value), 0, RowNo(TOTAL))), Year, Month))
Try this:
Pick(Dimensionality(),
Sum(Value),
Aggr(RangeSum(Above(TOTAL Sum(Value), 0, RowNo(TOTAL))), Year, Month),
RangeSum(Above(TOTAL Sum(Value), 0, RowNo(TOTAL))))
Hi Sunny,
I want to have the value in column 1 for A, B, C, D and the cumulative value for Total.
The correct values will be
2017 | 1 | A | 1 |
B | 2 | ||
C | 3 | ||
D | 4 | ||
Total | 10 | ||
2 | A | 5 | |
B | 6 | ||
C | 7 | ||
D | 8 | ||
Total | 36 | ||
3 | A | 9 | |
B | 10 | ||
C | 11 | ||
D | 12 | ||
Total | 78 | ||
4 | A | 13 | |
B | 14 | ||
C | 15 | ||
D | 16 | ||
Total | 136 | ||
Total | 136 |
Regards
Luca Jonathan Panetta
This:
If(Dimensionality() <> 2, Sum(Value),
Aggr(RangeSum(Above(TOTAL Sum(Value), 0, RowNo(TOTAL))), Year, Month))
I found solution.
Pick(Dimensionality(),
Sum(Value),
Aggr(RangeSum(Above(TOTAL Sum(Value), 0, RowNo(TOTAL))), Year, Month),
Sum(Value)
)
Thanks Sunny for your help. I have change only the 3th expression from RangeSum(Above(TOTAL Sum(Value), 0, RowNo(TOTAL))) to Sum(Value)
Best regards.
Luca Jonathan Panetta
Thanks Sunny.
I mark your answer as correct because your idea helped me to solve the problem.
But I like more Pick function than not IF.
Best regards
Luca Jonathan Panetta
If you like Pick more, you can always mark your own response as correct. You can mark my responses as Helpful if you want (you don't have to)
Qlik Community Tip: Marking Replies as Correct or Helpful
Best,
Sunny