topic Re: Previous three months in QlikView

Previous three months

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

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

Re: Previous three months

kaushiknsolanki — Tue, 08 Mar 2016 18:55:30 GMT

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

Re: Previous three months

sunny_talwar — Tue, 08 Mar 2016 18:56:44 GMT

May be this:

Converted your table into a pivot table

Re: Previous three months

mossandon — Tue, 08 Mar 2016 19:55:40 GMT

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?

Re: Previous three months

sunny_talwar — Tue, 08 Mar 2016 20:01:24 GMT

Is this the kind of table you envision?

Re: Previous three months

mossandon — Tue, 08 Mar 2016 20:10:30 GMT

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

It is possible?

Re: Previous three months

mossandon — Tue, 08 Mar 2016 20:45:57 GMT

Many Thx Kaushik,

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

Re: Previous three months

sunny_talwar — Tue, 08 Mar 2016 20:47:14 GMT

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

Re: Previous three months

erjohnso — Tue, 08 Mar 2016 20:51:08 GMT

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.

Re: Previous three months

mossandon — Tue, 08 Mar 2016 20:56:30 GMT

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

Re: Previous three months

kaushiknsolanki — Wed, 09 Mar 2016 07:26:00 GMT

Hi,

What output you are looking for and what you are getting.

Regards,

Kaushik Solanki

Re: Previous three months

jagan — Wed, 09 Mar 2016 07:38:41 GMT

HI,

In that case you need to create four expressions one for each month.

Regards,

jagan.

Re: Previous three months

sunny_talwar — Wed, 09 Mar 2016 09:53:28 GMT

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)

Re: Previous three months

mossandon — Wed, 09 Mar 2016 11:35:20 GMT

Many thanks Sunny T.

Your help will allow me to continue learning about this powerful tool.

And thanks to all who helped.

Regards

Mauricio