Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

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.

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

Tags (2)
1 Solution

Accepted Solutions
antoniotiman
Not applicable

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)

13 Replies
devarasu07
Not applicable

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)

antoniotiman
Not applicable

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

Not applicable

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.


Thanks for your help,

Ilaria

antoniotiman
Not applicable

Re: Count function with two conditions QlikSense

Can You provide Your sample data ?

Not applicable

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.

example.jpg

antoniotiman
Not applicable

Re: Count function with two conditions QlikSense

See Attachment

Not applicable

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.

antoniotiman
Not applicable

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)

Not applicable

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.