Show selected month and previous in a pivot table

[Patrick4]

Hi there, 

I have searched the forum, but after a long search I still haven't found a solution.

I have a pivot table where my Measures is the row and the date as YearMonth as Columm.

When a month is selected, I want the selected and previous month to be displayed in the pivot table. How can I make this work?

Many greetings
Patrick

[Oleg_Troyansky]

Hi Patrick,

This is a tricky one! I got the desired result using the following formula:

sum({<MonthYear={"=only({1} MonthYear)>=AddMonths(max(total MonthYear), -1) and only({1} MonthYear)<=max(total MonthYear)"}>} Amount)

It would take me pages and pages of explanation to describe everything that's going on here.  Some of it, like the tip of the iceberg, is described in this blog article, check it out.

To learn more advanced development techniques, including advanced Set Analysis and AGGR, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

Cheers,

[Or]

That looks like it'll work (and I agree, there's a ton going on there). However, wouldn't it be feasible to run along the lines of a more simple:

{$} + {<MonthYear={"=AddMonths(max(MonthYear),-1)"} >}

To union the current selection with the extra month needed? GetFieldSelections() could also be used instead of Max() for slightly better performance, I think?
Just curious, since the approach you suggested looks like it'll work fine.

[Oleg_Troyansky]

Hi Or,

A slightly modified version of your suggested formula should work, but not quite "as is" - have you tried it yourself? There are a couple of minor issues there, that are too subtle to be described here.

Cheers,