topic Current Month & Previous Month in QlikView

Current Month & Previous Month

Anonymous — Thu, 04 Feb 2016 18:02:53 GMT

Having some issues getting the Previous Month. I have tried these as Variables and Expressions.

creating a MONTH column from another field Incident Created Timestamp.
date(MonthStart([Incident Created Timestamp]), 'MM-YYYY') as Month,

Current Month

=Max(Month) returns 01 - 2016

Previous Month

=Max(Month) -1 returns 42369

=Max(Month-1) returns 42369

=Max((Month) -1, 'MM-YYYY') returns 42369

=Max((Month -1), 'MM-YYYY') returns 42369

=Max(Month -1, 'MM-YYYY') returns 42369

Re: Current Month & Previous Month

sunny_talwar — Thu, 04 Feb 2016 18:27:31 GMT

Try using AddMonths() function:

=AddMonths(Max(Month), -1)

Re: Current Month & Previous Month

Anonymous — Thu, 04 Feb 2016 19:21:57 GMT

Sunny T,

That got me closer (12/01/2015). Is there a way to get it in "MM-YYYY" format?

Then the next step I need is to COUNT the [Incident Identifier] based on the Variables that I created:
vCurrentMonth =Max(Month)

vPrevMonth =AddMonths(Max(Month), -1)

This works when I use the exact Previous Month - =Num(Count({<Month={"12-2015"}>}[Incident Identifier]))

This returns 0 - =Num(Count({<Month={"$(vPrevMonth)"}>}[Incident Identifier]))

This returns "error in set modifier ad hoc element list - =Num(Count({<Month={$(vPrevMonth)}>}[Incident Identifier]))

Re: Current Month & Previous Month

sunny_talwar — Thu, 04 Feb 2016 19:37:50 GMT

May be this for changing the date format.

=Date(AddMonths(Max(Month), -1), 'MM-YYYY')

Re: Current Month & Previous Month

Anonymous — Thu, 04 Feb 2016 19:55:41 GMT

That worked.

Re: Current Month & Previous Month

sunny_talwar — Thu, 04 Feb 2016 19:59:03 GMT

Awesome

Re: Current Month & Previous Month

Anonymous — Thu, 04 Feb 2016 21:06:33 GMT

The next step I need is to COUNT the [Incident Identifier] based on the Variables that I created:

vCurrentMonth =Max(Month)

vPrevMonth =AddMonths(Max(Month), -1)

This works when I use the exact Previous Month - =Num(Count({<Month={"12-2015"}>}[Incident Identifier]))

This returns 0 - =Num(Count({<Month={"$(vPrevMonth)"}>}[Incident Identifier]))

This returns "error in set modifier ad hoc element list - =Num(Count({<Month={$(vPrevMonth)}>}[Incident Identifier]))

Re: Current Month & Previous Month

sunny_talwar — Thu, 04 Feb 2016 21:10:05 GMT

It is still isn't working?

Re: Current Month & Previous Month

fcecconi — Thu, 04 Feb 2016 21:24:23 GMT

Dan

Do you use a master calendar in your app? For me, I prefer to create a master calendar, then have flags for current month, previous, etc. Then use set analysis to create my measures.

Regards,

Frank