topic Set Analysis in QlikView

Set Analysis

Thu, 11 Oct 2012 19:05:59 GMT

Hi Guys

I have a table say with the following fields

ID,Date1,Date2

i want to change my expression : Count(if(Date1 = Date2,ID))

into Set Analysis....

Is it possible???

Gysbert_Wassenaar — Thu, 11 Oct 2012 19:35:11 GMT

You can, but why do you want that?

I think the set analysis expression would be: count( {<[Date1]={"=[Date1]=[Date2]"}>} ID)

v_iyyappan — Thu, 11 Oct 2012 19:36:02 GMT

Hi,

Try like this

= Count({<DateFieldNum1={'=$(=Num(DateFieldNum2))'} ID)

Where

In Script

DateFieldNum1 = Floor(Num(DateFieldNum1))

DateFieldNum2 = Floor(Num(DateFieldNum2))

Regards,

johnw — Thu, 11 Oct 2012 19:42:49 GMT

I wouldn't do it with the if OR with set analysis. For performance, I'd suggest setting a counter in the script load like this:

if(Date1 = Date2, 1) as "Dates Equal Counter",

Or possibly more generally useful, as it could be used as a chart dimension, for instance, to only show IDs where the dates are equal:

if(Date1 = Date2, ID) as "Dates Equal ID",

Then experiment to find out which of the following is faster:

count("Dates Equal ID")
sum("Dates Equal Counter")

Different QlikView versions have differed in that regard.