13 Replies Latest reply: Aug 5, 2017 12:47 PM by Antonio Mancini

# Count function with two conditions QlikSense

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.

[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

• ###### Re: Count function with two conditions QlikSense

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)

• ###### Re: Count function with two conditions QlikSense

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

• ###### Re: Count function with two conditions QlikSense

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.

Ilaria

• ###### Re: Count function with two conditions QlikSense

Can You provide Your sample data ?

• ###### Re: Count function with two conditions QlikSense

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.

• ###### Re: Count function with two conditions QlikSense

See Attachment

• ###### Re: Count function with two conditions QlikSense

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

• ###### Re: Count function with two conditions QlikSense

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)

• ###### Re: Count function with two conditions QlikSense

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.

• ###### Re: Count function with two conditions QlikSense

Issue is Yor Dates are TimeStamp

and autoCalendar doesn't work properly.

I suggest You to create Date Like

Date(Floor([Data avvio UAT pianificata])) as [Data avvio UAT pianificata]/effettiva

Date of Enc...17620 is 30/06/2017 15:11:19 that is > 30/06/2017

• ###### Re: Count function with two conditions QlikSense

However try this

Count(If([Data avvio UAT pianificata] >= MonthStart(Today(),-2) and [Data avvio UAT pianificata] <= MonthEnd(Today(),-2) and

(Floor([Data avvio UAT effettiva]) > Floor([Data avvio UAT pianificata])

or Len(Trim([Data avvio UAT effettiva]))=0), Number))

• ###### Re: Count function with two conditions QlikSense

IT IS WORKING NOW!!! I created the dates as you suggested with Floor in the script, than upload again the data and the same formula as before is now working!

Just a last thng :  what do LEN and TRIM in the following formula? Len(Trim([Data avvio UAT effettiva]))=0

Thanks a lot for your help and patience.

Have a nice weekend,

Ilaria

• ###### Re: Count function with two conditions QlikSense

This is a way to get Null Values (or Blank)

Regards,

Antonio