Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try this
Count(DISTINCT {<verificationStatus={'Valid'}>} If(YearEnd(verificationDate) >= FDLink and verificationDate <= TDLink, ExtIDDevice))
Try this may be
Count(DISTINCT {<verificationDate={"<=$(date(TDLink))>=$(date(FDLink))"}, verificationStatus={'Valid'}>} ExtIDDevice)
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...
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)
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
Try this
Count(DISTINCT {<verificationStatus={'Valid'}>} If(YearEnd(verificationDate) >= FDLink and verificationDate <= TDLink, ExtIDDevice))
Yeah, this works!
But why it does not work in set analysis?
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