Hi,
I need to show the sum of a value over a dimension but only if the sum of those values are above a limit over some other dimensions.
Lets say I have this example data:
row
| month | version | network | total_amount | sub_amount |
|---|
| 1 | 9 | 1 | a | 9 | 4 |
| 2 | 9 | 2 | a | 30 | 4 |
| 3 | 10 | 1 | a | 45 | 7 |
| 4 | 10 | 2 | a | 50 | 9 |
| 5 | 10 | 3 | a | 10 | 5 |
| 6 | 10 | 2 | b | 50 | 8 |
| 7 | 11 | 3 | a | 20 | 9 |
| 8 | 11 | 4 | a | 15 | 4 |
| 9 | 11 | 4 | b | 100 | 10 |
| 10 | 11 | 5 | b | 40 | 2 |
| 11 | 12 | 5 | a | 70 | 9 |
| 12 | 12 | 6 | b | 10 | 8 |
| 13 | 12 | 7 | b | 70 | 9 |
I only want to use the total_amount and sub_amount if the sum of total_amount for the month and network is above a certain limit, lets say 40. Then I want to show the sum of the included values in a new table where I only have the month as dimension.
In this example it should be as follows:
Row 1 and 2: Should not be included in the calc since 39 (9+30) is below 40.
Row 3, 4, 5: Should be included in the calc since 105 (45+50+10) is above 40.
Row 6: Should be included in the calc since 50 is above 40.
Row 7, 8: Should not be included in the calc since 35 (20+15) is below 40.
Row 9, 10: Should be included since 140 (100+40) is above 40.
Row 11: Should be included since 70 is above 40.
Row 12, 13: Should be included since 80 (10+70) is above 40.
The result table shall look like this:
month
| sum(total_amount)
| sum(sub_amount)
|
|---|
| 10 | 155 (sum of rows 3, 4 ,5 ,6) | 29 |
| 11 | 140 (sum of rows 9, 10 since 7 and 8 shall be excluded) | 12 |
| 12 | 150 (sum of rows 11, 12, 13) | 26 |
The closest I have come up with is to set month as dimension and the following as expressions:
sum({<total_amount={"=aggr(sum(total_amount), month, network)>=40"}>} aggr(sum(total_amount), month, network))
sum({<total_amount={"=aggr(sum(total_amount), month, network)>=40"}>} aggr(sum(sub_amount), month, network))
However, as those expessions seem to give the correct result in this small example it will not give the correct result always. In my real data it will present some month as "-".
Any suggestions?
