Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

nbrusova
New Contributor II

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
MVP
MVP

Re: Dates in Set Analysis issue

Try this

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

7 Replies
Highlighted
MVP
MVP

Re: Dates in Set Analysis issue

Try this may be

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

nbrusova
New Contributor II

Re: Dates in Set Analysis issue

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...

MVP
MVP

Re: Dates in Set Analysis issue

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)

nbrusova
New Contributor II

Re: Dates in Set Analysis issue

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

MVP
MVP

Re: Dates in Set Analysis issue

Try this

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

nbrusova
New Contributor II

Re: Dates in Set Analysis issue

Yeah, this works!

But why it does not work in set analysis?

MVP
MVP

Re: Dates in Set Analysis issue

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