topic Re: Last 2 Month Count in QlikView

Last 2 Month Count

rahulsingh12 — Tue, 24 Apr 2018 05:53:40 GMT

Hi,

I have created an expression in which if we select a month it counts the unique outlet from Current and last month.

But if we add the month in the table it starts showing the unique outlet for individual month.

e.g

State #Count

AP 85

State Month #Count

AP 1 67

AP 2 100

I want the output to be showing unique outlet for the current month and last month for each month not individual unique outlet for each month.

Thus if for 3 and 2 we have 100 outlet

2 and 1 month we have 85 unique outlet then output should be like

State Month #Count

AP 2 85

AP 3 100

Re: Last 2 Month Count

sunny_talwar — Tue, 24 Apr 2018 09:19:30 GMT

Would you be able to share a sample where you are facing this issue for us to play around with?

Re: Last 2 Month Count

rahulsingh12 — Wed, 25 Apr 2018 04:57:24 GMT

Hi Sunny,

I am attaching the Qlikview file for the reference.

In the chart I made I wanted unique outlet count for current and last month not just for individual month as it is coming currently.

Thus in month 2 I should 6 not 3.

Regards,

Re: Last 2 Month Count

tresB — Wed, 25 Apr 2018 06:24:08 GMT

Shouldn't it be 8 instead (for month 2)? Try exp like:

=RangeSum(Above(Count({<Month={">=$(=$(vMaxMonthOneLess))<=$(=$(vMaxMonth))"}>} distinct OutletCode) ,0,2))

Re: Last 2 Month Count

rahulsingh12 — Wed, 25 Apr 2018 06:33:06 GMT

Hi,

We have 5 unique in Month 1 and one unique 7 from month from 2 thus 6 unique combining 1 and 2 month

Re: Last 2 Month Count

tresB — Wed, 25 Apr 2018 07:57:14 GMT

Ahh, the old problem. There has been a lengthy discussion on this community over this issue. With sortable aggr(), this could probably be handled I guess (never tried; just a guess). Is a script solution an option for you?

Re: Last 2 Month Count

sunny_talwar — Wed, 25 Apr 2018 09:15:27 GMT

Implementing an AsOfTable might help here

Outlet:

LOAD * INLINE [

OutletCode, Month

1, 1

2, 1

3, 1

4, 1

5, 1

1, 2

3, 2

7, 2

12, 3

1, 3

3, 3

7, 3

];

OutletToState:

LOAD * INLINE [

OutletCode, State

1, AP

2, KAR

3, TN

4, HR

5, GUJ

6, J&K

7, MAHA

12, JHAR

];

AsOfTable:

LOAD Month as AsOfMonth,

Month - IterNo() + 1 as Month

Resident Outlet

While Month - IterNo() + 1 >= 0;

Re: Last 2 Month Count

rahulsingh12 — Wed, 25 Apr 2018 09:28:40 GMT

Hi Sunny,

Thanks for the reply.

The file I have shared is very low version of the code I have in actual file.

By any chance will it be possible to do it directly in chart expression.

Regards,

Rahul

Re: Last 2 Month Count

sunny_talwar — Wed, 25 Apr 2018 11:52:39 GMT

Sure thing, but remember this will make things extremely complicated...

=RangeSum(ABove(

Sum({<Month={">=$(=$(vMaxMonthOneLess))<=$(=$(vMaxMonth))"}>}Aggr(If(Only({<Month={">=$(=$(vMaxMonthOneLess))<=$(=$(vMaxMonth))"}>}OutletCode) = Above(Only({<Month={">=$(=$(vMaxMonthOneLess))<=$(=$(vMaxMonth))"}>}OutletCode)), Null(),

Count({<Month={">=$(=$(vMaxMonthOneLess))<=$(=$(vMaxMonth))"}>} OutletCode)), OutletCode, (Month, (NUMERIC)))), 0, RowNo()))

Also, this will only work if you have QV12 or above... or else you will need to sort Month field in the script in the ascending order.... if it is already sorted like that... then you can try this

=RangeSum(ABove(

Count({<Month={">=$(=$(vMaxMonthOneLess))<=$(=$(vMaxMonth))"}>} OutletCode)), OutletCode, Month)), 0, RowNo()))

Re: Last 2 Month Count

rahulsingh12 — Thu, 26 Apr 2018 10:38:51 GMT

Hi Sunny,

Thanks for the reply.

But the formula seems to work when I do the selection of the particular month.

Like if I select 2 it shows 6 but if I don't select any month or select 3 then the number of outlet for 2 becomes 3 again.

Regards,

Re: Last 2 Month Count

sunny_talwar — Thu, 26 Apr 2018 11:03:15 GMT

Try this

=RangeSum(ABove(

Sum({<Month>}Aggr(If(Only({<Month>}OutletCode) = Above(Only({<Month>}OutletCode)), Null(),

Count({<Month>} OutletCode)), OutletCode, (Month, (NUMERIC)))), 0, RowNo())) * Avg({<Month={">=$(=$(vMaxMonthOneLess))<=$(=$(vMaxMonth))"}>} 1)