Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Patrick4
Contributor
Contributor

Show selected month and previous in a pivot table

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

Patrick4_2-1687805055591.png

 

 

Labels (2)
3 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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
MVP
MVP

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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,