topic Complex Aggr in QlikView

Complex Aggr

Sat, 04 Feb 2012 12:57:38 GMT

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

Complex Aggr

Sat, 04 Feb 2012 18:04:33 GMT

Try this

2 dimensions : Year , Month

expression :

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

Regards

Re: Complex Aggr

Sat, 04 Feb 2012 18:35:15 GMT

Hi, this can help you:

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

Regards

Complex Aggr

Sat, 04 Feb 2012 19:42:58 GMT

Thank you JJ, thank you thefourth.