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

[paulyeo11]

My QV Doc

[sunny_talwar]

Paul where is our sample brother

[paulyeo11]

Hi Sunny

See my second posting. that i attach sample.

Pau;

[sunny_talwar]

May be this:

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

[paulyeo11]

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

[paulyeo11]

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

[paulyeo11]

Hi All

I mean how to make 151 display as 36 ?

[sunny_talwar]

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

[paulyeo11]

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