topic Dates in Set Analysis issue in App Development

Dates in Set Analysis issue

Anonymous — Wed, 01 Nov 2017 09:04:18 GMT

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

Re: Dates in Set Analysis issue

sunny_talwar — Wed, 01 Nov 2017 11:24:21 GMT

Try this may be

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

Re: Dates in Set Analysis issue

Anonymous — Wed, 01 Nov 2017 11:42:59 GMT

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

Re: Dates in Set Analysis issue

sunny_talwar — Wed, 01 Nov 2017 11:51:46 GMT

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)

Re: Dates in Set Analysis issue

Anonymous — Wed, 01 Nov 2017 12:23:21 GMT

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

Re: Dates in Set Analysis issue

sunny_talwar — Wed, 01 Nov 2017 12:38:03 GMT

Try this

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

Re: Dates in Set Analysis issue

Anonymous — Wed, 01 Nov 2017 13:04:42 GMT

Yeah, this works!

But why it does not work in set analysis?

Re: Dates in Set Analysis issue

sunny_talwar — Wed, 01 Nov 2017 13:12:12 GMT

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