Pivot Table - Max Visible

[pkelly]

Hi....

I have a pivot table which uses the following formula to calculate the MAT...

rangesum(after(sum(IF(cma_Region = '01 - Scotland', mat_Sales)),0,12))

This works fine...

The issue that I have is that my pivot table shows 25 months where I only want to show current plus 12.

I need all of these months to be present so that the caclulation works but I don't want them all displayed.

In line charts you have the ability to limit what is being displayed via the Presentation tab Max Visible option.

I cannot see this option for pivot tables and was wondering if there is another way to achieve this?

At present I have narrowed the width of the pivot table to only show the months I want but, when I put the table on a report, I am having scaling issues.

Any assistance greatly appreciated...

Regards

Paul

[matt_crowther]

Paul,

I've used a technique in the past that may be of use, sure you can easily adapt to your needs:

=if(week(Date)<=week(today()) and week(Date)>week(today())-3,week(Date),'Others')

As a calculated dimension it tidies that data up but doesn't exclude any values.

Hope that is of some use at least,

Matt - Visual Analytics Ltd

[pkelly]

Thanks matt,

Have given this a go..

It prevents the months appearing but unfortunately also affects the results being produced.

Regards

Paul

[matt_crowther]

Paul,

Do you have a sample data set aas this sounds and feels as though it should be possible.

I'm assuming removing the date control element from the expression (thus leaving the control to the dim) doesn't work either?

All the best,

Matt - Visual Analytics Ltd

[pkelly]

Hi Matt

Apologies for the delay...

Please find attached example..

Regards

Paul