Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Partner
Partner

Hide expression column in pivot table

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.

clipboard_image_0.png

I have also attached the qvw file

Labels (1)
5 Replies

Re: Hide expression column in pivot table

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')

MVP & Luminary
MVP & Luminary

Re: Hide expression column in pivot table

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

Partner
Partner

Re: Hide expression column in pivot table

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

MVP & Luminary
MVP & Luminary

Re: Hide expression column in pivot table

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.

clipboard_image_0.png

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

Support
Support

Re: Hide expression column in pivot table

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

To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.