Set analysis : combining conditions

[#1]

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

[Gysbert_Wassenaar · Accepted solution]

Something like this perhaps:

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

[#3]

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]

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 )

[#5]

Thanks everyone, both solution works well !