topic Re: Previous month set analysis help please... in QlikView

Previous month set analysis help please...

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have an expression which works fine for the selected Month to Date however I cannot ssem to get the syntax correct to replicate it for the previous Month to date:

Current working MTD expression:

sum({<[Year]={"$(=Year(Max([Date])))"},[Mnth]={"$(=Month(Max([Date])))"},[Date]={"<=$(=Date(Max([Date]), 'DD/MM/YYYY'))"},[Channel]={"Agg"},[Product]={"Motor"},[Sub-Channel]={"*"}>}[Quoted])

[Mnth] is month name eg. Jan, Feb....

I cannot seem to get it to work for the previous month to date based on the date selection and think that it is something to do with the formatting of the result into 'MMM' that I can't seem to get right.

I also have [Month] as month number (1-12)

I have tried using [Month] and applying a -1 to the formula but to no avail.

Many Thanks in anticipation of your help.

Re: Previous month set analysis help please...

Gysbert_Wassenaar — Wed, 20 Jul 2016 15:23:52 GMT

Perhaps like this:

sum({<[Year]=,Mnth,[Month]=,[Date]={">=$(=Date(MonthStart(Max([Date]),-1), 'DD/MM/YYYY'))<=$(=Date(AddMonths(Max([Date]),-1), 'DD/MM/YYYY'))"},[Channel]={"Agg"},[Product]={"Motor"},[Sub-Channel]={"*"}>}[Quoted])

Re: Previous month set analysis help please...

Thu, 21 Jul 2016 09:56:05 GMT

That's Great.

Thanks very much.