Streamlining Formula

[pkelly]

I have a report where I have 5 pivot tables (4 business units and 1 total).

Each pivot table has 12 columns...

=sum(if(sin_HistoricalMonthNumber = 0 AND tap_Split ='Core', sin_Sales))

An example of the type of formula is shown above where "tap_Split" defines the business unit.

When replicating this pivot for the next business unit, I copy the table them modify the tap_Split criteria against each of the columns to say (for example) ....

=sum(if(sin_HistoricalMonthNumber = 0 AND tap_Split ='Regional', sin_Sales))

The formula is identical except for the tap_Split selection.

Is there an easier way to do this i.e. copy the pivot and change one entry to say that I want to just look at regional instead of core.

NB - I am aware that I can introduce a filter and just have one pivot table - the user wants to see all pivots at the same time [:#]

[stephencredmond · Accepted solution]

Not really.

If you need to hard code it, you need to hard code it.

If it was a bitmap chart (rather than a table) I might think about a trellis, but you don't have that option.

Stephen

[d_pranskus]

Hi

But I highly recommend you to use SET ANALYSIS, not IF function in your expressions.

What about a Pivot table With Month dimension and just one expression with Set Analysis restricting date range to 12 Months?

Cheers

Darius

[pkelly]

Thanks Darius....

Reason this is in as an IF as opposed to using set analysis is that this was written in version 8.2 - set analysis wasn't available in this version.

We have only just move (this month) to version 9 - one of the jobs I have on my desk is to rewrite all of our reports utilising the new functionality in version 9.