Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Previous month set analysis help please...

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.

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Previous month set analysis help please...

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])


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
MVP & Luminary
MVP & Luminary

Re: Previous month set analysis help please...

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])


talk is cheap, supply exceeds demand

View solution in original post

Not applicable

Re: Previous month set analysis help please...

That's Great.

Thanks very much.