topic Re: Add YTD totals in Pivot Table in QlikView

Add YTD totals in Pivot Table

Mon, 22 Feb 2016 17:30:04 GMT

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!

Re: Add YTD totals in Pivot Table

Oleg_Troyansky — Mon, 22 Feb 2016 17:36:20 GMT

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!

Re: Add YTD totals in Pivot Table

Mon, 22 Feb 2016 18:05:26 GMT

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

Re: Add YTD totals in Pivot Table

Oleg_Troyansky — Mon, 22 Feb 2016 18:35:43 GMT

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

Re: Add YTD totals in Pivot Table

Tue, 23 Feb 2016 16:44:55 GMT

Thank you - I will try that!

Re: Add YTD totals in Pivot Table

Anonymous — Tue, 23 Feb 2016 17:05:05 GMT

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.

Re: Add YTD totals in Pivot Table

Fri, 26 Feb 2016 15:02:06 GMT

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?

Re: Add YTD totals in Pivot Table

Oleg_Troyansky — Fri, 26 Feb 2016 18:16:30 GMT

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

Re: Add YTD totals in Pivot Table

Mon, 29 Feb 2016 14:29:56 GMT

Thank you for your help!

Re: Add YTD totals in Pivot Table

Oleg_Troyansky — Mon, 29 Feb 2016 19:56:35 GMT

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!

Re: Add YTD totals in Pivot Table

Thu, 03 Mar 2016 21:11:14 GMT

Thank you for your help. I know the expressions are way too complicated. That's just a sample of what my predecessor left for me!