Qlik Community

Qlik Sense App Development

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

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