17 Replies Latest reply: Mar 16, 2016 11:01 AM by Sunny Talwar

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

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

• ###### Re: How to convert count(distinct(if(\$(ColumnDim4),inv))) to SET ?

My QV Doc

• ###### Re: How to convert count(distinct(if(\$(ColumnDim4),inv))) to SET ?

May be this:

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

• ###### Re: How to convert count(distinct(if(\$(ColumnDim4),inv))) to SET ?

Hi sunny

Thank !

This i need to think thru , even the answer is correct. Some thing is wrong some where , let me check first.Let me mark correct first.

Paul

• ###### Re: How to convert count(distinct(if(\$(ColumnDim4),inv))) to SET ?

Hi Sunny

Accidentally I found the correct SET expression to count invoice. Below expression working fine for YTD :-

=count({\$<year = {\$(=max(year))},month = {"<=\$(=max({<year={\$(=max(year))}>} month))"}, company= {'=sum({\$<year = {\$(=max(year))}, month = {"<=\$(=max({<year={\$(=max(year))}>} month))"}>} sales)>1'}>} distinct inv)

But i have problem in figure out how to make the above expression convert the LY_YTD , so that it will 18 as correct value.

=count({\$<year = {\$(=max(year)-1)},month = {"<=\$(=max({<year={\$(=max(year)-1)}>} month))"}, company= {'=sum({\$<year = {\$(=max(year)-1)}, month = {"<=\$(=max({<year={\$(=max(year)-1)}>} month))"}>} sales)>1'}>} distinct inv)

I have try it display 151 , is wrong.

Paul

• ###### Re: How to convert count(distinct(if(\$(ColumnDim4),inv))) to SET ?

Hi All

I mean how to make 151 display as 36 ?

• ###### Re: How to convert count(distinct(if(\$(ColumnDim4),inv))) to SET ?

Do you want the count for the whole month of March (42) or until Today (36) for the last year?

• ###### Re: How to convert count(distinct(if(\$(ColumnDim4),inv))) to SET ?

Hi Sunny

Can you make it , if i never select any date , it will count the jan till mar.

If i want to get jan till Feb i will click on month = 2 yr = 16

Paul

• ###### Re: How to convert count(distinct(if(\$(ColumnDim4),inv))) to SET ?

March 31st or March 15th?

• ###### Re: How to convert count(distinct(if(\$(ColumnDim4),inv))) to SET ?

Hi sunny

To March 15.

Wow you can control the SET to very well

Paul

Sent from my iPhone

• ###### Re: How to convert count(distinct(if(\$(ColumnDim4),inv))) to SET ?

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)

• ###### Re: How to convert count(distinct(if(\$(ColumnDim4),inv))) to SET ?

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 ?

• ###### Re: How to convert count(distinct(if(\$(ColumnDim4),inv))) to SET ?

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)

• ###### Re: How to convert count(distinct(if(\$(ColumnDim4),inv))) to SET ?

Hi sunny

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

You are good.

Paul

Sent from my iPhone

• ###### Re: How to convert count(distinct(if(\$(ColumnDim4),inv))) to SET ?

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

• ###### Re: How to convert count(distinct(if(\$(ColumnDim4),inv))) to SET ?

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

• ###### Re: How to convert count(distinct(if(\$(ColumnDim4),inv))) to SET ?

Paul where is our sample brother

• ###### Re: How to convert count(distinct(if(\$(ColumnDim4),inv))) to SET ?

Hi Sunny

See my second posting. that i attach sample.

Pau;