Unable to write Set Analysis Expression for if condition

pradeep92 — Thu, 11 Nov 2021 06:09:18 GMT

Hello,

Requirement:

Need to Create a table to count the IDs where the difference between Rolling date and Last updated date is between 0 to 6.

I have created a Table object with Dimension as RollingDate and measure as below and it works perfectly

count(distinct if((RollingDate-LAST_UPDATED_DATE<=6 and RollingDate-LAST_UPDATED_DATE>=0 ), ID))

But when I try to write with Set analysis, it shows 0 as measure with dimension RollingDate

Count({<RollingDate={"=RollingDate <= (LAST_UPDATED_DATE + 6)"}>}ID).

Kindly help me to create a table with dimension RollingDate and measure with Set analysis.

Thanks!

Re: Unable to write Set Analysis Expression for if condition

AshutoshBhumkar — Thu, 11 Nov 2021 06:21:10 GMT

Hello,

I would suggest you to create a flag in the script like below.

E.g if(RollingDate-LAST_UPDATED_DATE<=6 and RollingDate-LAST_UPDATED_DATE>=0,1,0 ) as Flag

And use that flag in Set Analysis.

Count(Distinct {<Flag = {'1'}>}ID)

Thanks,

Ashutosh