Is it possible to have field on a pivot table showing data outside of current selections?

[#1]

Hello...

I have a pivot table which shows Budget/Actuals by Product and by Month, the date ranges are based on current selections.

Usually users will select the current year's figures to display.

I would like to have a extra field which always shows the 2009 (ie previous year) Actual total.

I've attached a mock up in Excel showing what I'm after...

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/4762.Independent-Field-on-Pivot-Table.xlsx:550:0]

I tried adding this as an expression (which works kinda well) but to sum the 2009 total you need to select 2009 AND 2010 data...

Any pointers appreciated! Mike

[#2]

Hi,

try to use SET Analysis. See QV help for some more examples.

sum( {1<Year = {2010}>} Sales )

And here you are able to use "$(=Year(Today())-1)" instead of 2010 fix.

sum( {1<Year = {$(=Year(Today())-1)}>} Sales )

Good luck!

Rainer

[#3]

Thanks Rainer that great... I read up about SA and it's now looking good...

I've set up an Expression using sum( {1<Year = {2009}>} Sales ) and it's 90% there,

Could you help me figure out the last 10%??

- The 2009 total shows under each month (monthly total) however I just need the year total on the left hand side of all the monthly data (see xlsx example).

- After applying the Set Analysis expression all the 2009 months are displayed in the PT rather than just the ones physically selected.

Many thanks, Mike

[#4]

Hi Mike,

maybe you have only change "1" to "$" in the expression (first part).

sum( {$<Year = {$(=Year(Today())-1)}>} Sales )

Set Identifiers

There is one constant that can be used to denote a record set; 1. It represents the full set of all the records in the application.

The $ sign represents the records of the current selection.

Rainer

[#5]

Thanks again Rainer... I'm starting to understand SA a little more now but the 1 -> $ constant does affect those two issues...

[#6 · Accepted solution]

Mike,

another idea is "Dynamic Dimension".

See the attached example.

Rainer

[#7]

Thanks for the example...

That worked a treat... even handles a cyclic group as a dimension!

Checking the 'alway expand' on the presentation tab even hides the little boxes...

Thanks so much for your help! Mike

[#8]

Hi

Its good