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

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

paulyeo11 — Wed, 16 Mar 2016 12:11:36 GMT

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 ?

paulyeo11 — Wed, 16 Mar 2016 12:12:20 GMT

My QV Doc

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

sunny_talwar — Wed, 16 Mar 2016 12:12:47 GMT

Paul where is our sample brother

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

paulyeo11 — Wed, 16 Mar 2016 12:19:06 GMT

Hi Sunny

See my second posting. that i attach sample.

Pau;

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

sunny_talwar — Wed, 16 Mar 2016 12:21:59 GMT

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 ?

paulyeo11 — Wed, 16 Mar 2016 12:37:23 GMT

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 ?

paulyeo11 — Wed, 16 Mar 2016 13:22:49 GMT

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 ?

paulyeo11 — Wed, 16 Mar 2016 13:26:32 GMT

Hi All

I mean how to make 151 display as 36 ?

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

sunny_talwar — Wed, 16 Mar 2016 13:40:49 GMT

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 ?

paulyeo11 — Wed, 16 Mar 2016 13:48:36 GMT

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 ?

sunny_talwar — Wed, 16 Mar 2016 14:00:23 GMT

March 31st or March 15th?

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

paulyeo11 — Wed, 16 Mar 2016 14:03:41 GMT

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 ?

sunny_talwar — Wed, 16 Mar 2016 14:09:28 GMT

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 ?

paulyeo11 — Wed, 16 Mar 2016 14:28:43 GMT

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 ?

sunny_talwar — Wed, 16 Mar 2016 14:36:33 GMT

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 ?

paulyeo11 — Wed, 16 Mar 2016 14:54:05 GMT

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 ?

paulyeo11 — Wed, 16 Mar 2016 14:58:02 GMT

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 ?

sunny_talwar — Wed, 16 Mar 2016 15:01:32 GMT

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