topic Re: Current month vs previous month issue in QlikView

Current month vs previous month issue

mahitham — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

Can any one please help me on below issue.

I have used below expressions for current and previous month kpis

Current:

=Sum(Aggr(Count({<[Month Name]= {'$(=MonthName(Max([Date])))'}, Product = {"*"}-{' '}>} Distinct id), [Month Name],Product))

Previous:

=Sum(Aggr(Count({<[Month Name]= {'$(=MonthName(AddMonths(Max([Date]),-1)))'},Product = {"*"}-{' '}>} Distinct id), [Month Name],Product))

By default its showing the results correctly

When I have selected april previous kpi changed to zero

Please help me on this.

Thanks in advance

Re: Current month vs previous month issue

ogautier62 — Tue, 22 May 2018 11:37:59 GMT

Hi,

it's I think because you make a selection

so max(date) is april, and there's notthing selected for max(date) - 1

try with {1 to select all without taking account of selection for month-1

regards

Re: Current month vs previous month issue

mahitham — Wed, 23 May 2018 06:34:23 GMT

Could you please suggest any way to resolve the above issue.

Thanks in advance.

Re: Current month vs previous month issue

tresB — Wed, 23 May 2018 06:39:42 GMT

Try putting the month selection by-pass in set analysis in aggr(), like:

Previous:

=Sum(Aggr({< [Month Name]>} Count({<[Month Name]= {'$(=MonthName(AddMonths(Max([Date]),-1)))'},Product = {"*"}-{' '}>} Distinct id), [Month Name],Product))

Re: Current month vs previous month issue

jyothish8807 — Thu, 24 May 2018 04:54:36 GMT

Hi Mahitha,

Your expression looks correct to me.

May be try to see the output of "=MonthName(AddMonths(Max([Date]),-1))" or create a variable for max Month name and previous month name and use in the expression.

Else you can try to bypass the monthname field as suggested by tresesco‌.

Br,