topic Re: Hide expression column in pivot table in QlikView

Hide expression column in pivot table

VivenReddy — Sat, 16 Nov 2024 02:32:31 GMT

I have a pivot table in which I'd like the Total [$ Volume] column displayed. However I do not want the [$ Volume] columns displayed under the pivoted Supplier columns. See screenshot below.

I have also attached the qvw file

Re: Hide expression column in pivot table

rubenmarin — Sun, 08 Sep 2019 10:57:02 GMT

Hi, a workaround can be moving the expression as a calculated dimension:

=If(RowDim=1,aggr(Num(sum(Total_kg * Price_kg),'$#,##0;-$#,##0'),Fruit, Category))

It won't show totals, maybe you can simulate them if you insert another RowDim to show totals and simulated them setting bakground color and bold text format.

The calculated dimension for Fruit can be:

=Pick(RowDim, Aggr(Fruit, Fruit), 'Others')

Re: Hide expression column in pivot table

marcus_sommer — Mon, 09 Sep 2019 13:47:24 GMT

Another approach could be just to add another on-top dimension to the object. If none native hierarchycally one exists you could also simply use something like: = 'all' and then you could with (secondary)dimensionality() refer to it to apply different expressions for each level.

Also possible is adding a further value to your dimension in the script, like:

concatenate(INLFED) load '$ Volume' as Supplier autogenerate 1;

and then you could query this fieldvalue to branch into different expressions.

- Marcus

Re: Hide expression column in pivot table

VivenReddy — Tue, 10 Sep 2019 16:08:43 GMT

Thanks for the response. Are you able to show me your proposal on my sample qvw file.

Re: Hide expression column in pivot table

marcus_sommer — Wed, 11 Sep 2019 07:44:36 GMT

Here both approaches whereby the second suggestion with adding an appropriate dummy-value within the script might need some further adjustments (adding appropriate values within other fields, too to be to consider any selections and/or those fields within the aggr and/or the totals) because of your rather complex design with the aggr.

Beside this I would suggest to keep the logic as simple as possible and avoiding the aggr and the various other stuff with the col/dim and so on and to build a rather "classical" datamodel and also calculating the volume and similar measures already within the script.

- Marcus

Re: Hide expression column in pivot table

Brett_Bleess — Thu, 19 Sep 2019 15:02:46 GMT

Viven, did Marcus' updated app get you what you needed? Please be sure to give him credit if so by using the Accept as Solution button on his post. If you did something else, please consider posting that and then using the button to mark that post as the solution. If you are still working on things, leave an update of where things stand.

Regards,
Brett