topic Re: Set analysis help in QlikView

Set analysis help

Sun, 26 May 2013 07:54:39 GMT

Hi all,

I have an if statement, which I'd like to convert into Set Analysis.

The expression is : count(distinct if(LAST_UPDATE_DATE_MAX=[Last Update Date],OrderNumber ))

I tried this is in Set analysis, but it doesn't return any thing.

=count(distinct {<LAST_UPDATE_DATE_MAX={"$(=[Last Update Date])"} OrderNumber))

I'd like to return if, it the one date field(Last Update Date Max) equals another date field (Last Update Date)

Any ideas as to what I'm doing wrong?

Re: Set analysis help

Miguel_Angel_Baeyens — Sun, 26 May 2013 08:02:00 GMT

Hi,

First, my guess is that in this case what you need is not set analysis rather a row by row comparison. Set analysis is evaluated once, and before doing the aggregations so the dataset is reduced to what set analysis specifies. In your formula, the left part is a field, that is OK, but the right part is a field as well, and my guess is that you want a value, no a lot of values.

If my assumtiom is correct, then you should use instead

= {'$(=Max([Last Update Date]))'}

Note that as mentioned this will compare the left part field to the value of that Max function for the whole chart, not row by row.

Hope that helps.

Miguel

Re: Set analysis help

swuehl — Sun, 26 May 2013 10:24:17 GMT

Try something like

=count(DISTINCT {<OrderNumber = {"=[Last Update Date]=LAST_UPDATE_DATE_MAX"}>} OrderNumber)

assuming that all three fields reside in the same table in your data model.

Instead of OrderNumber, you may need to use a field with the granularity required for your field value comparison (so that last update date and last update date max return an unambiguous answer).

Re: Set analysis help

Mon, 27 May 2013 04:53:32 GMT

Hi,

try using

count({<LAST_UPDATE_DATE_MAX={"$(=[Last Update Date])"} >} DISTINCT OrderNumber)

Hope that helps.

mrunmayi

Re: Set analysis help

Tue, 28 May 2013 14:18:40 GMT

Thanks for all of your answers.

I'm using it in a text object on my dashboard, but it returns a 0.

Re: Set analysis help

Tue, 28 May 2013 14:41:49 GMT

If both are fields and may have, in a specific moment, more than 1 possible or selected value, try with this expression:

count({<LAST_UPDATE_DATE_MAX=p([Last Update Date]) >} DISTINCT OrderNumber)

But I'm not 100% sure if the field in the left is that or the other one. Anyway, try it and see what happens .

Cheers,

Borja