Show only sum of values for a dimension if sum is above limit based on other dimension

[Anonymous]

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?

[antoniotiman · Accepted solution]

Try this

Dimension : month

Expressions :

Sum(Aggr(If(Sum(total_amount) > 40,Sum(total_amount)),month,network))

Sum(Aggr(If(Sum(total_amount) > 40,Sum(sub_amount)),month,network))

[Anonymous]

Thank you Antonio,

It seems to work perfect