# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Rules, plus terms and conditions, can be found here.
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
MVP

## 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)

17 Replies
Valued Contributor II

My QV Doc

MVP

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

Paul where is our sample brother

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;

MVP

## 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)

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

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

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 ?

MVP

## 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?

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