Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
March 31st or March 15th?
Hi sunny
To March 15.
Wow you can control the SET to very well
Paul
Sent from my iPhone
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)
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 ?
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)
Hi sunny
Thank you very much , this is what I am looking for.
You are good.
Paul
Sent from my iPhone
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
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