Last three months count in Month wise table

[chanchalsharma]

Hi All ,

I have a requirement I have to show Month wise customer count in table where in one expression need to display count of customer code and in next expression need to display customer count of  last 3 month in respect of that month .

For example if first row for Jan 2015 ,last 3 month count of customer would be Nov ,Dec and Jan .

in next row Feb 2015 ,last three month would be Dec ,Jan and Feb .

I used rangesum function but it is giving me sum of last 3 row which is incorrect  .

    RangeSum(above(Count(distinct{<_Flag={'SD WOD_DOD'}>}CustomerCode),0,3))

then I pass condition for last 3 month in set analysis but it is giving same value as month wise value .

  Count(distinct{< _Flag={'SD WOD_DOD'},CommonDate ={">=$(=num(MonthStart(Date(Max(CommonDate)-90))))<=$(=num(MonthStart(Date(Max(CommonDate)))))"}>}CustomerCode)

Please check both expresion which I used ,help me out .

If any another solution can be provided, would very helpful .

Thanks in Advance

Chanchal

[chanchalsharma]

Hi Kaushik ,

Thanks for your reply .

I applied you solution it is giving last three month count .

But it is giving another challenge that is how do I show month wise count .

I have to show both in single table .

Please guide .

Thanks in Advance

[kaushiknsolanki · Accepted solution]

Hi,

Change this script.

InterMediate:

  Load InterMonth as InterMonth,

   '$(vMonth)' as Show_Month_Chart,

   If(InterMonth = '$(vMonth)',1,0) as Flag

  Resident CAL where InterMonth >= AddMonths($(vMonth),-2) and InterMonth <= $(vMonth);

In chart Add below expression to get monthly count.

Count(Distinct {<_Flag={'SD WOD_DOD'},Flag = {"1"}>} CustomerCode)

Hope this will solve the issue.

Regards,

Kaushik Solanki

[brunobertels]

Hi

I'am not sure to understand what do you mean by wise month ( due to my poor english )

But have a look to this post about accumulation :

https://community.qlik.com/blogs/qlikviewdesignblog/2015/11/09/accumulations

for your case it will be very usefull i think

Regards

Bruno

[reddy-s]

Hi Chanchal,

Making use of master calendar should be the best way to go as it will even help with your performance.

Use inMonths() function to achieve the last 3 months flag and later this can be made use of in your calculations.

Thanks,

Sangram.

[chanchalsharma]

Many Thanks Kaushik ,

Your solution is working  fine.

If possible to you ,Please explain how this logic is working?

Thanks in Advance

Chanchal

[kaushiknsolanki]

HI Chanchal,

The logic goes like this.

1. You wanted last 3 months count, which as I had said was not possible with expressions in chart, so I have done modification in script and against each month I have mapped last 3 months. So using the new month you can easily get last 3 months count.

2. you wanted the Month wise count also, so I have created a flag which will select the current month from the mapping and thus you will get exact count for that month also.

Hope its clear.

Regards,

Kaushik Solanki