Pivot table - display expression total only

[NickHoff]

I have a pivot table with three dimension levels, which displays as:

I created 4 expressions. I want to show under each classification.  This part works correctly.

The problem is I have 4 additional expressions 5-8, which i'd like to display as a total outside of the classification.  How can I go about achieving this?

[Gysbert_Wassenaar]

That's not possible. The expressions will always be shown for all dimension combinations. You can try creating calculated dimensions from the expressions you want to show as totals. You'll have to aggregate those over the dimensions in the first two columns in your screenshot. The calculated dimensions can not be positioned on the right of the table. They will be shown between the other dimension columns/rows.

Perhaps it's easier to create an additional straight table with expressions 5 to 8 and position that straight table to the right of the pivot table in such a way that the pivot table covers the dimension columns of the straight table.

[NickHoff]

Isn't there a way using dimensionality()?  I'd have to use a pivot table since the subtotals of the medical/surgical and factors need to be included as well as the grand total.  However, I lose out on the grand total since the second pivot table would only include 2 dimensions.

Also, if the user filtered on one of the classifications by clicking the pivot table the other classifications would close displaying the full second table.

[kuba_michalik]

You know what, actually, due to the fact you have 4 expressions there and you need 4 special total columns, this should work. Or something like work . Set the table to show partial sums for the horizontal (Classification) dimension and use SecondaryDimensionality() function. It will return 1 for your normal column, and 0 for the Total columns, of which there will be 4, because you have 4 basic expressions. Use this to customize with If() what you want to show in the base column, and what in the Total columns. One thing I cannot figure out and might be impossible to do, is to customize expression label based on whether you are showing it in total section, or in normal section. Expression labels seem to be evaluated outside of table context.

One other alternative that comes to mind is to have an inner synthetic dimension in lieu of expression labels, and a single actual expression (with all the caveats - it will be a single expression, so all the columns will be the same width etc). I don't even want to think about performance of this one, though.

[Anonymous]

Hi Nick,

Did you find the solution to your requirement. If so please post it here. Im having a similar business requirement.

Thanks in advance

Suchitra

[NickHoff]

I didn't find a great solution, but I do have a work around.  I created two pivot tables and lined them up correctly.   The second pivot table I created an INLINE table for the values and used a PICK expression to calculate the totals of each inline option which could be found in the first table.  This caused an issue where chrome and IE would display the tables in different locations, so I had to create variable to check which browser the user was using and display an error message if they were using anything older than ie8.  Not the prettiest work around but that's what you get when trying to get Qlikview to behave like excel on different data sets.