Solved: Re: Previous three months - Qlik Community

mossandon · ‎2016-03-08

Hi friends,

I need to show sales for the three previous months to the selected month,

In the example show a month earlier, but do not know how to show the other two previous months.

I appreciate your help.

Thx in advance

sunny_talwar · ‎2016-03-09

Here you go. Back to straight table with 4 expressions and 1 dimension:

Expressions

1) sum({$<Year, Month, Date = {"$(='>=' & Date(MonthStart(Max(Date))) & '<=' & Date(MonthEnd(Max(Date))))"}>} Sales)

2) sum({$<Year, Month, Date = {"$(='>=' & Date(MonthStart(Max(Date), -1)) & '<=' & Date(MonthEnd(Max(Date), -1)))"}>} Sales)

3) sum({$<Year, Month, Date = {"$(='>=' & Date(MonthStart(Max(Date), -2)) & '<=' & Date(MonthEnd(Max(Date), -2)))"}>} Sales)

4) sum({$<Year, Month, Date = {"$(='>=' & Date(MonthStart(Max(Date), -3)) & '<=' & Date(MonthEnd(Max(Date), -3)))"}>} Sales)

View solution in original post

kaushiknsolanki · ‎2016-03-08

HI,

Try this

Sum({<Date = {">=$(=monthstart(addmonths(Max(Date),-1)))=$(=Monthend(addmonths(Max(Date),-1)))"},Year=,Month=>}Sales)

For previous month

Sum({<Date = {">=$(=monthstart(addmonths(Max(Date),-2)))<=$(=Monthend(addmonths(Max(Date),-2)))"},Year=,Month=>}Sales)

For Previous to Previous Month and so on.

Regards,

Kaushik Solanki

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

sunny_talwar · ‎2016-03-08

May be this:

Converted your table into a pivot table

mossandon · ‎2016-03-08

Thx Sunny T,

It works ok, but I need to use other dimensions than the month...I forget to add

I incorporated new dimension called "Region" in the attached example.

you could help me with that dimension?

sunny_talwar · ‎2016-03-08

Is this the kind of table you envision?

mossandon · ‎2016-03-08

Yes, but the dimension must only be "Region" no "MonthYear".

It is possible?

mossandon · ‎2016-03-08

Many Thx Kaushik,

But I get an erroneous result by applying the expression, and is independent of the selection.

sunny_talwar · ‎2016-03-08

You don't want to see MonthYear? Like this?

erjohnso · ‎2016-03-08

You can also potentitally use the max(fieldname, rank) function in your set analysis.

For example, max(Month, 2) would give you the "prior" month if the current month is the max month. max(Month, 3) and max(Month, 4) would be the 2 and 3 months back from current.

mossandon · ‎2016-03-08

Yes I need to see the month year but not included as used dimension: