Complex Aggr

[#1]

Hi All,

I'm looking for an aggr expression that would sum amounts if sum of these amounts by name, year and month are less than 200 - see below example. Important thing - expression will work in pivot table with only two dimenions: year and month.

Input:

Name, Year, Month, Product, Amount

John, 2011, 8, A, 150

John, 2011, 8, B, 30

Mary, 2011, 9, C, 160

Mary, 2011, 10, D, 170

Mary, 2011, 10, D, 40

Output:

Year, Month, Sum(Amount)

2011, 8, 180

2011, 9, 160

Regards,

Przemek

[#2 · Accepted solution]

Hi

Try this

2 dimensions :  Year , Month

expression :  

sum(   if( aggr( sum(Amount) , Name , Year , Month) < 200 , aggr( sum(Amount) , Name , Year , Month) , null()))

Regards

JJ

[#3]

Hi, this can help you:

if(aggr(sum(Amount),Month,Year)<200,aggr(sum(Amount),Month,Year))

Regards

[#4]

Thank you JJ, thank you thefourth.