Dates in Set Analysis issue

[Anonymous]

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

[sunny_talwar]

Try this may be

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

[Anonymous]

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]

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 · Accepted solution]

Try this

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

[Anonymous]

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