topic Re: Set analysis : combining conditions in QlikView

Set analysis : combining conditions

Fri, 13 Sep 2013 15:02:46 GMT

Hi,

I have an issue with a report i'm not able to build :

I have as input a list of incidents (IncidentID) with for each opening (DStart) and closing (DEnd) dates.

I'm looking to build an expression that combine the to following needs and count each incidents that :

Have been closed the same month of the opening ( monthstart(DEnd) = monthstart(DStart) )

and have a duration : DEnd - DStart < 4hours

Thanks in advance

Re: Set analysis : combining conditions

Gysbert_Wassenaar — Fri, 13 Sep 2013 16:12:25 GMT

Something like this perhaps:

count({<IncidentID={'=monthstart(DEnd)=monthstart(DStart) and DEnd-DStart<1/6'}>} IncidentID)

Re: Set analysis : combining conditions

Fri, 13 Sep 2013 16:16:30 GMT

Gilles,

I am not sure you can perform this kind of Set Analysis, as Set Analysis only gets evaluated one time prior to the calculation. I don't think there is a way for Set Analysis to vary on a row by row basis.

Depending on the size of your dataset, you could use an expression similar to the following:

=count(if ([DEnd]-[DStart]<4, if(monthstart(DEnd]) = monthstart(DStart), [IncidentID])))

This could cause performance issues on a very large dataset. Alternatively, you could introduce a change to your load script that flags each row based on whether or not it was closed in the same month as it was opened, and also add a calculated field with the duration. Then you could combine two Set Analysis modifiers for these new fields.

Re: Set analysis : combining conditions

Clever_Anjos — Fri, 13 Sep 2013 16:18:37 GMT

I would calculate 2 flags at script level

1) if(monthstart(DEnd) = monthstart(DStart) ,1,0) as FlagSameMonth

2) if( DEnd - DStart < 4,1,0) as FlagDuration

So, your expression could be

count({<FlagSameMonth={"1"},FlagDuration={"1"}>}IncidentID )

Re: Set analysis : combining conditions

Mon, 16 Sep 2013 13:30:18 GMT

Thanks everyone, both solution works well !