Pivot Chart- Calculation time when using Variables to hide Dimensions

[#1]

Hi all. I have a Pivot Chart where the dimensions are calculated depending on whats in a variable e.g. 'when Variable1='1' then Dimension1, when Variable1='2'  then Dimension2' etc etc. This works correctly.

My issue is with the calculation time. If the user changes the variable from say 1 to 2 it takes quiet along time for the table to refresh. It currently says in the Sheet properties that the calculation time for the chart is '9641'. Which i presume is a lot?

Before i added the calculated dimension it worked perfectly.  Whats confusing me even more is that when i actually just move the Pivot Chart from one position on the sheet to another (a simple drag) the table refreshes / reloads.

Is there some setting i can turn off to stop this refresh constantly happening? Or make the Pivot Chart more efficient?

thanks in advance

[Anonymous]

Hi,

Maybe you can upload an example of your doc so that the good folks here in the community can take a look and see if they spot anything interesting?

[#3]

Thanks for getting back to me Johannes. Example attached below.

If you try chaning the period or simply move the table it takes some time to respond.

thanks,

[#4]

Anyone have any ideas?!!!

[prodanov]

Hi,

  Try this function to calculate dimension:

=pick(Variable1,one_month_grade

,two_month_grade

,Three_month_grade

,Four_month_grade

,Five_month_grade

,Six_month_grade

,Seven_month_grade

,Eight_month_grade

,Nine_month_grade

,Ten_month_grade

,Eleven_month_grade

,Twelve_month_grade

)

By the way on my computer report work fine. Restart computer and try again.