Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to achieve this set analysis

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.

3 Replies
swuehl
MVP
MVP

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

So maybe

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

Not applicable
Author

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
MVP
MVP

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