Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

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

1 Solution

Accepted Solutions
sunny_talwar

Does 16 makes sense when you select month = 2?

Capture.PNG

Expression

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

View solution in original post

17 Replies
paulyeo11
Master
Master
Author

My QV Doc

sunny_talwar

Paul where is our sample brother

paulyeo11
Master
Master
Author

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
Master
Master
Author

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
Master
Master
Author

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
Master
Master
Author

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
Master
Master
Author

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