Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
chanchalsharma
Contributor III
Contributor III

Last three months count in Month wise table

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

1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

15 Replies
Anonymous
Not applicable

try this?

=Count(distinct{< CommonDate= ,_Flag={'SD WOD_DOD'},

CommonDate ={">=$(=Date(Max(CommonDate)-90,'DD/MMM/YYYY'))<=$(=Date(Max(CommonDate),'DD/MMM/YYYY'))"} >} CustomerCode)


Note: Format of Common Date should be 'DD/MMM/YYYY'



kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

HI,

Second expression should work, but all you need to do is suppress the selections of other filters related to the year, month , quarter...

So your expression will be like below.

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


Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Anonymous
Not applicable

Use of Num() will work only if CommonDate is into numeric form, else it will not work

chanchalsharma
Contributor III
Contributor III
Author

Hi All ,

Thanks a lot for quick response.

I have checked  expression share by both of you . But it is not giving correct answer .

I have attached output of data for reference please check column C and D.

Thanks in Advance

Chanchal

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Can you please upload sample application and also let us know what output you are looking for (With values)

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
chanchalsharma
Contributor III
Contributor III
Author

Yes Sure ,

Please find attached file .

Also I have mentioned last  3 month values for 3 months .

 

DecNovOct
823 841 871

Thanks in Advance

Chanchal

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Can you please explain how did you got the numbers like Dec 823..

What is the logic behind this.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
chanchalsharma
Contributor III
Contributor III
Author

Sure .

823 is distinct count of customer code of Dec ,Nov and Oct month for December.

In Excel I did this mannualy .

Same number I need in qlik in Last 3 months .

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

I have made some changes in script to make it work.

It cant be achieve using set analysis, because the Max or any other aggregate function in  set analysis is evaluated at overall level not at dimension level.

Hope this will solve your issue.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!