Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

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)

View solution in original post

13 Replies
devarasu07
Master II
Master II

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
Master III
Master III

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
Author

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
Master III
Master III

Can You provide Your sample data ?

Not applicable
Author

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
Master III
Master III

See Attachment

Not applicable
Author

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

antoniotiman
Master III
Master III

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
Author

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.