Show/hide partial sum

[posywang]

Hi,

We got a user request to allow users to show/hide partial sum on particular dimension they select. I created duplicate dimensions in pivot table and assigned conditional by using a variable to show/hide dimension with or without partial sum enabled. However, I still haven't figured out a smart way to let user show/hide dimension on particular dimension they select. I don't want to create many show/hide buttons to control each dimension. Sample QVW is attached. Thanks!

[posywang]

This is awesome! I removed vShow from script since we really don't need it. Can you please let me know what is the logic behind the expression below? Trying to understand your script so I can leverage the same technique in the future. Many thanks for your help!

SubStringCount('|' & GetFieldSelections(_dimension2, '|,|', 1000) & '|', $(=Chr(39) &'|' & FirstSortedValue(_dimension,_dimensionPosition,1) & '|' & Chr(39))) = 1

[sunny_talwar]

Short answer is that it is checking if you have selected one of the _dimensions2 which is also selected in _dimension. For more details

I am collecting all selections in _dimension2 using GetFieldSelections. The pipes are just there to make it Robust, because at times you may have a selection which is a subset of another selection. For example if you have Category and SubCategory, things get bad if you don't use the pipes. To see what the difference is, try two text boxes

1) ='|' & GetFieldSelections(_dimension2, '|,|', 1000) & '|'

2) =GetFieldSelections(_dimension2, ',', 1000)

The next part is just using the dimension from your _dimension pick and checking if any of those are also selected in _dimension2 using SubStringCount. When selected, the output equals 1, when not selected, output equals 0.

HTH

Best,

Sunny

[chtat61]

there is no condition for show/hide partial sum, but you can emulate it

take a copy of table/chart and don't set show partial sum

set the both table/chart at the same left/top 

in the one, with partial sum, you set a condition for show (chart->layout::show conditional)

in the other, without partial sum, you set a condition for show, too

e.g. i have a pivot-table for 2 years, with sum and difference (use SecondaryDimensionality)

if(SecondaryDimensionality()=0, thisYearSum - prevYearSum, sum)

pivot-table with difference to show, has a conditional show = not(GetSelectedCount(Jahr)=1)

pivot-table without difference to show, has a conditional show = (GetSelectedCount(Jahr)=1)

if both years are display, i get a values with difference, otherwise only values of year will be showed.

regards