topic Re: Show/hide partial sum in QlikView

Show/hide partial sum

posywang — Thu, 26 Jan 2017 18:17:56 GMT

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!

Re: Show/hide partial sum

sunny_talwar — Thu, 26 Jan 2017 18:44:30 GMT

So where do you plan to pick the dimensions for which you want to show or hide subtotals?

Re: Show/hide partial sum

posywang — Thu, 26 Jan 2017 18:54:56 GMT

Not sure if we can create a second list box (doesn't have to be) for user to select the dimension? I don't know. Feel free to design anything that works.

Re: Show/hide partial sum

sunny_talwar — Thu, 26 Jan 2017 19:54:38 GMT

See if the attached is good for you.... The second list box (which is in an alternate state) will only show up when you select Show Subtotal and then you can decide which dimensions you want to show subtotal on.

Re: Show/hide partial sum

posywang — Thu, 26 Jan 2017 21:19:26 GMT

Thanks for the quick response. The attached doesn't seem to work... Show/hide button only show/hide the new list box. Both duplicate dimensions were displayed.

Re: Show/hide partial sum

sunny_talwar — Thu, 26 Jan 2017 21:43:40 GMT

Are you using QlikView 11.2 SR7 or before?

Re: Show/hide partial sum

posywang — Thu, 26 Jan 2017 21:45:24 GMT

Version 11.20.12451.0 SR7

Re: Show/hide partial sum

sunny_talwar — Thu, 26 Jan 2017 21:47:37 GMT

That is why it did not work.... can you rerun the application for me with another field which is exact copy of _dimensions?

LOAD _dimension as _dimension2,

_dimensionPosition as _dimensionPosition2

Resident Dimension;

Re: Show/hide partial sum

posywang — Thu, 26 Jan 2017 21:52:25 GMT

Please see attached.

Re: Show/hide partial sum

sunny_talwar — Thu, 26 Jan 2017 21:58:20 GMT

See if this one works

Re: Show/hide partial sum

posywang — Thu, 26 Jan 2017 22:13:57 GMT

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

Re: Show/hide partial sum

sunny_talwar — Thu, 26 Jan 2017 22:25:53 GMT

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

Re: Show/hide partial sum

chtat61 — Wed, 26 Jun 2024 10:06:25 GMT

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