Test row data before aggregation.

[#1]

Hi I need help with an issue, I'm struggling to find a solution for this :

I need to test some values before being aggregated, like the example :

This is my data :

MonthName DateA	DateA	DateB	ValueA	ValueB
Jan 2012	05-Jan-12	10-Jan-12	1000	1000
Jan 2012	10-Jan-12	10-Jan-12	500	300
Feb 2012	10-Feb-12	05-Feb-12	2000	1500
Feb 2012	10-Feb-12	05-Feb-12	3000	3000

What I want to display :

MonthName DateA	Sum(ValueA)	Sum(ValueB)	Expression
Jan 2012	1500	1300	300
Feb 2012	5000	4500	4500

The Expression being:

if(DateB <= DateA, ValueB, 0)

The expression obviously doesn't work because the data was aggregated demanding me to aggregate the values, however, I can't see how I can filter the values before the aggregation, if I do :

if(Min(DateB) <= Min(DateA), Sum(ValueB), 0)

I'll get wrong data cause it will not compare the dates in the row, but rather all dates from the month, and either not filter any values or filter all, depending on the combination of max/min functions that I choose.

Does anyone know how to make this ?

I'd be grateful if you could post the solution, not the qvw, as I'm using a personal version and can't open other people's qvws.

Thanks in advance.

[rwunderlich · Accepted solution]

I would think this would work:

sum( if(DateB <= DateA, ValueB, 0) )

-Rob

[#3]

Such a simple solution, thanks a lot.