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: 
Anonymous
Not applicable

Dates in Set Analysis issue

Hi

I faced a problem with dates comparison in set analysis.

Here's an expression:

Count(distinct {<

verificationDate={"<=$(date(TDLink))"},

verificationDate={">=$(date(FDLink))"},

verificationStatus={'Valid'}>}ExtIDDevice)

and this expression doesn't work cause there are two rows to be excluded from the given result. I need verificationDate to be between FDLink and TDLink but I can't get it working.

Comparing FDLink and TDLink with now() gives the expected result.

Count has to be 208 for Nov 2017

1 Solution

Accepted Solutions
sunny_talwar

Try this

Count(DISTINCT {<verificationStatus={'Valid'}>} If(YearEnd(verificationDate) >= FDLink and verificationDate <= TDLink,  ExtIDDevice))

View solution in original post

7 Replies
sunny_talwar

Try this may be

Count(DISTINCT {<verificationDate={"<=$(date(TDLink))>=$(date(FDLink))"}, verificationStatus={'Valid'}>} ExtIDDevice)

Anonymous
Not applicable
Author

Unfortunately, didn't work.

Interesting that if you make a table with ExtIDDevice and this expression:=if(verificationDate <=TDLink and verificationDate>=FDLink, 1,0), after that select 1, count will be 208...

sunny_talwar

I have exported the data for Nov-2017, can you tell me which all rows from Nov-2017 data should be included to get 207?? (Highlight the rows in Yellow)

Anonymous
Not applicable
Author

I made another file - it's almost the same. it also contains the condition which I was telling about and verificationStatus.

Distinct count from highlighted rows on the ExtIDDevice field gives 208 for Nov 2017

sunny_talwar

Try this

Count(DISTINCT {<verificationStatus={'Valid'}>} If(YearEnd(verificationDate) >= FDLink and verificationDate <= TDLink,  ExtIDDevice))

Anonymous
Not applicable
Author

Yeah, this works!

But why it does not work in set analysis?

sunny_talwar

Set analysis is evaluated once per chart and what you needed was a row level check... so you need to use if or you can create a flag in the script and then use that flag in your set analysis