How to convert count(distinct(if($(ColumnDim4),inv))) to SET ?

[paulyeo11]

Hi All

I have below expression working fine , it return 28 or 36 depend on CY or YTD.

count(distinct(if($(ColumnDim4),inv)))

How to make the SET expression also return 36 ?

COUNT(distinct{$<year = {$(=Max(year)-1)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}inv/1000)

Paul

[sunny_talwar]

March 31st or March 15th?

[paulyeo11]

Hi sunny

To March 15.

Wow you can control the SET to very well

Paul

Sent from my iPhone

[sunny_talwar]

Then why don't you like this expression?

Count(distinct{$<year = {$(=Max(year)-1)}, date = {"<=$(=Date(AddYears(Max({<year={$(=Max(year))}, sales = {'*'}>} date), -1)))"}>}inv)

[paulyeo11]

Hi sunny

If you try to open the app and click on button 1 and 2 , i have indicate why i prefer the long SET expresssion ?

[sunny_talwar · Accepted solution]

Does 16 makes sense when you select month = 2?

Expression

Count(distinct{$<year = {$(=Max(year)-1)}, date = {"<=$(=Date(AddYears(Max({<year={$(=Max(year))}, sales = {'*'}>} date), -1)))"}, month>}inv)

[paulyeo11]

Hi sunny

Thank you very much , this is what I am looking for.

You are good.

Paul

Sent from my iPhone

[paulyeo11]

Hi Sunny

Any reasons why there are total 3 Max(year) in below expression , why the centre Max(year) you not add -1 ?

Count(distinct{$<year = {$(=Max(year)-1)}, date = {"<=$(=Date(AddYears(Max({<year={$(=Max(year))}, sales = {'*'}>} date), -1)))"}, month>}inv)

Paul

[sunny_talwar]

There is only two max(years). The third one with -1 is the max(date) and -1 is to get the same exact date for last year using AddYears() function