topic Re: How to achieve this set analysis in QlikView

How to achieve this set analysis

Sat, 20 Oct 2012 14:24:49 GMT

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.

Re: How to achieve this set analysis

swuehl — Sat, 20 Oct 2012 14:43:07 GMT

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

So maybe

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

Re: How to achieve this set analysis

Sat, 20 Oct 2012 14:56:23 GMT

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.

Re: How to achieve this set analysis

swuehl — Sun, 21 Oct 2012 22:13:42 GMT

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