Count function with two conditions QlikSense

[#1]

Hello,

I have a question about the set analysis below:

I have to calculate the number of Number field which met two conditions:

- Data avvio UAT pianificata must be between the Start and End of the Month and

- Data avvio UAT effettiva must be greater than Data Avvio UAT pianificata.

count({<[Data avvio UAT pianificata]= {'>=$(=AddMonths( MonthStart (Today()),-2))<=$(=AddMonths( MonthEnd (Today()),-2))'},

[Data avvio UAT effettiva]={'>$([Data avvio UAT pianificata])'}>}Number)

But it seems to count the number of Number field only taking into account the first condition.

Which is the error?

Thanks a lot for your help,

Ilaria

[devarasu07]

hi,

Can you try like below,

Count({<[Data avvio UAT pianificata]={">=$(=AddMonths(MonthStart (Today()),-2))<=$(=AddMonths(MonthEnd (Today()),-2))"} ,[Data avvio UAT effettiva]={">$([Data avvio UAT pianificata])"} >} Number)

[antoniotiman]

Hi Ilaria,

try this

Count({<[Data avvio UAT pianificata]={'>=$(=MonthStart(Today(),-2))<=$(=MonthEnd(Today(),-2))'},

Number={'=[Data avvio UAT effettiva] > [Data avvio UAT pianificata]'}>} Number)

Regards,

Antonio

[#4]

Hello Antonio,

I tried both your solution as well as Devarasu's but I am getting wrong results.

I don't know if it could depend from the first condition that should establish the time range for Data UAT pianificata.

I'll give you the scenario, so maybe there could be different solutions then mine:

I have an excel file with all the Numbers identifying a change then the dates when the UAT has been planned and executed. I have to calculate if the UAT is in delay or not for a specific month, in this case is June.

So to calculate the delay I need:

- Data avvio UAT pianificata must be between the Start and End of the Month (June) and Data avvio UAT effettiva must be greater than Data Avvio UAT pianificata, but also Data avvio UAT effettiva must be greater than Data Avvio UAT pianificata even if this one it has been planned in another month than June.

Thanks for your help,

Ilaria

[antoniotiman]

Can You provide Your sample data ?

[#6]

Hello,

here an example of the data I used. It is a file excel that I upload: I expect to have 10 items on delay for the UAT

(#AA16,AA17,AA20,AA21,AA22,AA30,AA32,AA37,AA39) Also when the UAT effective is missing I consider it as a non-UAT so a delay.

[antoniotiman]

See Attachment

[#8]

I tried your formulas in my app, but i am getting a wrong result. In attachment what I did.

[antoniotiman · Accepted solution]

Try this

Count({<[Data avvio UAT pianificata]={'>=$(=MonthStart(Today(),-2))<=$(=MonthEnd(Today(),-2))'},

Number={'=Floor([Data avvio UAT effettiva]) > Floor([Data avvio UAT pianificata]) or Len(Trim([Data avvio UAT effettiva]))=0'}>} Number)

[#10]

I tried and I got 8 on delay instead of 10, there are two missing even if they have a UAT planned date but an empty UAT effective date.

I attached the screenshot, it seems that when n a table you enter the Delay measure all the two dates field got empty.