Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
paulyeo11
Valued Contributor II

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

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

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)

17 Replies
paulyeo11
Valued Contributor II

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 ?

Paul where is our sample brother

paulyeo11
Valued Contributor II

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

Hi Sunny

See my second posting. that i attach sample.

Pau;

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)

paulyeo11
Valued Contributor II

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

paulyeo11
Valued Contributor II

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

paulyeo11
Valued Contributor II

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?

paulyeo11
Valued Contributor II

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

Community Browser