Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis : combining conditions

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

Something like this perhaps:

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


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

Something like this perhaps:

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


talk is cheap, supply exceeds demand
Not applicable
Author

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.

Clever_Anjos
Employee
Employee

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 )

Not applicable
Author

Thanks everyone, both solution works well !