Add YTD totals in Pivot Table

[#1]

Hi - Here's a snapshot of my pivot table:

As you can see, the bottom yellow row should be YTD totals, but instead it is always a subtotal of the selected fields. Where and how do I change that value?

Thank you!

[Oleg_Troyansky]

You can modify the formula for each total using the function Dimensionality(). For example:

IF(Dimensionality() = 0, one calculation,

IF(Dimensionality() = 1, another calculation,

...

))

Find out what Dimensionality number represents the YTD Total and replace the usual formula with the YTD calculation there.

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy and Austin, TX!

[#3]

I can't find that dimensionality. It's an automatic total, and I can't figure out where the expression is.

[Oleg_Troyansky]

Dimensionality() is a function. It returns 0 for the chart total, 1 for the first subtotal, etc...    To find out the specific level, create an extra expression with this function:

=Dimensionality()

and see what is the number in the desired total position.

cheers,

Oleg Troyansky

[#5]

Thank you - I will try that!

[Anonymous]

To add the  chart function dimensionality() as suggested by Oleg, you need to go to the 'Expressions' tab. There, inside the existing expression(formula) you have for each measure (eg: Waste %, Waste Lbs, Packed Lbs), you can add an IF condition that will first evaluate the value of dimensionality() for a given row and then will use the expression you want. Month and Week are your dimensions in the chart. When there is a need to calculate metrics on a different range but in the same column in the table, dimensionality() can help achieve that.

Read more here: http://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/ChartFunctions/InterRecordFunctio...

[#7]

I can't figure out how to use Dimensionality to accomplish this.

The definition is "Dimensionality() returns the number of dimensions for the current row. In the case of pivot tables, the function returns the total number of dimension columns that have non-aggregation content, that is, do not contain partial sums or collapsed aggregates."

All of my columns contain partial sums and collapsed aggregates. All I want is for the YTD total in the bottom row to be a YTD total regardless of what selections are chosen. What am I missing?

[Oleg_Troyansky]

Lee,

this description that you quoted is very cryptic and doesn't describe what Dimensionality() function does in reality.

In order to understand how it works, add a new expression and use Dimensionality() as the formula. Then, see what numbers do you get in your totals, subtotals, and detailed rows. You should see 0 for the chart total, 1 for the first dimension's subtotal, etc...

The way it's used is this:

IF (Dimensionality() = 0, sum({<YTD condition>} Sales),

IF(Dimensionality() = 1, <another calculation for the first subtotal>,

Sum(Sales) ))    // Default calculation for all non-totals,

If you can post your app, someone could help you build the formula.

cheers,

Oleg Troyansky

[#9]

Thank you for your help!

[Oleg_Troyansky · Accepted solution]

Hi Lee,

wow, your expressions are way too complicated. I won't have enough time to fix all the aggregations that you have there. I modified the first expression and replaced the total value by 100%, using Dimensionality(). You will need to take if from here and replace it with your YTD formula. I also modified the background of the same expression to red, demonstrating one more time how to use Dimensionality.

A few general comments:

- Your data model may need to be restructured to enable simpler calculations. Adding up dozens of separately named transaction fields is a maintenance nightmare, it needs to be simplified by a lot!

- try to get away from calculated dimensions, they hurt performance. Your application won't fly on a larger scale.

- in QlikView, the most important principle to remember is SIMPLICITY - try to simplify everything in your application, and your job will get a lot easier.

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy and Austin, TX!