How to achieve this set analysis

[#1]

Hi All,

I have the following records:

load * Inline

[Id, TestDate,StartDate

1,'2012/01/01','2012/01/05'

2,'2012/01/02','2012/01/03'

3,'2012/01/03','2012/01/01'

];

I want to use pivot table to show the data:

TestDate as the dimension, and use set analysis to show how many records whose StartDate is greater than TestDate.

I use this expression, count({<StartDate={'>TestDate'}>}Id), but it does not work, how to figure it out?

Thanks.

[swuehl]

You want to compare StartDate > TestDate record wise (per Id), right?

So maybe

=count({<Id= {"=StartDate>TestDate"}>} Id)

[#3]

Thanks swuehl,

It works now. Just out of curiosity, why the expression I provided does not work?

And why need one equal sign in your expression, I mean the equal sign before "StartDate",

{"=StartDate>TestDate"}

Would you please help me out again?

Thanks.

[swuehl]

Your expression is telling something like "search all StartDate '>TestDate' and Select them".

You've got some issues here:

a) TestDate is not evaluated as expression in this case, so you compare StartDate values with the literal 'TestDate'

b) Even if you could could compare StartDate with the corresponding TestDate, what should be selected if you woudl have another record,

4,'2012/01/01','2012/01/01'

The answer, if StartDate Jan 1st should be selected would be ambiguous, right?

So, whenever you want to do record based comparisons, you should select in a field that has a unique value per record. In your example, I assume field Id shows unique values per record.

The equal sign is now telling QV to evaluate StartDate>TestDate (in the context of each Id). This results in a logical value, all true values will trigger the selection of current Id value (like "search all Id values where StartDate > TestDate and select these Id").

Hope this helps,

Stefan