PIVOT Table - can we have total logic different than formula for items?

[elenaramurica]

Hi all

I have the below metrics (MIX% and VOL%). My issue it with the MIX% TOTAL by customer.

• In pivot table it usses subtotals for the source columns and applies the same formula we have for items. (RED font for subtotals MIX%)
• What I need (for MIX% only) is simple subtotals for the weekly results in MIX% ( correct result as highlighted in yellow background).

Ultimateley I need a summary by customer with option to expand / colapse to further categories. This is why flat table can't help. 

Can this be done in QLIKSENSE at all? Thank you!

[Or]

You can use Dimensionality() for this.

If(Dimensionality() = 0, 'This would be the logic for grand total',

If(Dimensionality() = 1, 'This would be the logic for Total by Customer',

'This would be the logic for Rows'))

Note that these formulas can break if you make changes to the pivot's dimensions, reorder the dimensions, etc. so be aware of that.

[elenaramurica]

Thanks for the very quick feedback.

It says in help that this function desn't work for PIVOT TABLES which is exactly what I need.

I tried to creae a chart to show correct totals but doesn't seem to work (error in expression).

My formulas and source pivot table below. subtotals for CUSTMER and site are NOK as I need different formula than what pivot used.

[Or]

You seem to have misread. It only works for pivot tables (or rather, it's only really useful for pivot tables).

Do note that you need to get the dimensionality right for your specific use case. It's helpful to simply add a measure showing the dimensionality so you can make sure you're using the right numbers. You may also need to adjust your formulas to have the dimensionality() inside the aggregation, depending on assorted factors.

[elenaramurica]

Sorry, I am a beginer...

• MIX (for Dimensionality 3) = column(4)*column(5)
• what I need for MIX Totals (Dimensionality 1 or 2)  = sum(MIX result for dimensionality 3). In my example sum of numbers on green = 0.3%

Using Dimensionality() I managed to get totals & subtotals  = ZERO. How should I adapt the formula to get the result I need (0.3% instead of 0).

Thank you!

[Or · Accepted solution]

I have no way of answering that question since I don't know what all these columns represent. I'd suggest starting a different thread with the specific question of how to calculate totals/subtotals based on another subtotal so you can get fresh eyeballs on a fresh question. Odds are it'll involve some sort of aggr() formula.