Dynamic Total in Pivot Table

[Tim_G]

Hello there,

I woul like to calculate the total of a row in a pivot table. Usually this works well with "Sum(total <TYPE> SALES)"

In my example I use it to calculate the market share such as follows:

Sum([SALES_EUR])
/Sum(total <YEAR,COUNTRY> [SALES_EUR])

However this total by TYPE is fixed on the dimensions YEAR,COUNTRY here. If I want wo change dimensions such that e.g. PRODUCTGROUP is a row and COUNTRY becomes a column than I get incorrect totals.

If I move PRODUCTGROUP to the left by drag and drop, then I would like to become the formualr "Sum(total <PRODUCTGROUP> [SALES_EUR])" dynamically? Maybe dimensionality() could help but I could not figure it out yet.

Thanks in advance, Tim

[rubenmarin]

Hi, maybe you could use GetObjectDimension() to dynamically change the fields included in total, but it could be difficult if the number od horizontal and vertical dimensions changes.

It could be something like:

Sum(total <$(=GetObjectDimension (0))$(=If(not IsNull(GetObjectDimension (1)),',' &GetObjectDimension (1))> [SALES_EUR])

[Tim_G]

Hi, that was a nice hint!

I created a Master Dimension such that it always yields the Name of the lowest dimension. However, this is done with N if statements where N is the maximum number of dimensions possible in a our pivot table. Let's make it 5 here:

If(not isnull(GetObjectDimension (4)),GetObjectDimension (4),
If(not isnull(GetObjectDimension (3)),GetObjectDimension (3),
If(not isnull(GetObjectDimension (2)),GetObjectDimension (2),
If(not isnull(GetObjectDimension (1)),GetObjectDimension (1),
GetObjectDimension (0)))))

I call this dimension DIMHELP. The next step however would be the formular to total over this dimension like this:

=Sum(total <[DIMHELP]> SALES_EUR)

Unfortunately this does not work. Do you have any thought on this?

This post here deals with this issue, however I do not really know what is done here. They say one should load the Master Dimension in the Load Script. I can't find any instructions how to do that. But I suppose we are close to a solution.

https://community.qlik.com/t5/App-Development/Count-TOTAL-lt-Master-Dimension-gt-Product/td-p/45839

[rubenmarin]

Hi,I don't think that post manages the cahange of horizontal to vertical dimensions.

The option I proposed was to use the $-expansion to create the TOTAL fields inside the same TOTAL clause, and in a way that is manages change of the number of dimensions.

Have you tested it as I posted?

[Tim_G · Accepted solution]

Yes, it works as you suggested, even though qlik yields an error which confused me. Thank you very much!

In a next step I try to match it the Dimensionality in order to expand the <TYPE> part dynamically. I tried something like this but it does not work:

Sum(total <$(=GetObjectDimension (0))$(=If(Dimensionality() >= 2, ',' & GetObjectDimension (1)))>[SALES_EUR])

So similarly to your expression it should add another <TYPE> Dimension if we have at least 2 row dimensions in a pivot table. Do you have any suggestions on what I missed?

(I could also add your part "not IsNull(GetObjectDimension (1))" to the expression but I suppose it is not needed if we are able to work with Dimensionality. The GetObjectDimension part seems to not account for switching a dimension from column to row.)

[rubenmarin]

Hi, I don't think Dimensionality() would work inside a $-expansion because it's expanded before the cells are calculated, maybe with this workaround:

=If(Dimensionality()>=3
,Sum(total <$(=GetObjectDimension(0))$(=','& GetObjectDimension(1))> [SALES_EUR])
,Sum(total <$(=GetObjectDimension(0))> [SALES_EUR])
)

[Tim_G]

Hey, thanks for the workaround!

Unfortunately it doesn't work with the if(Dimensionality() part. The Sum expression is totally fine. Do you have an idea?

[rubenmarin]

Hi, you need to close the If, with a parenthesys at the end.

Also note that my answer has another excpression for the 'else' part of the if.

[Tim_G]

I did that, even if it is not visible in the screenshot. The full expression is as follows:

If(Dimensionality()>=2,
,Sum(total <$(=GetObjectDimension(0))$(=','& GetObjectDimension(1))> [SALES_EUR])
,Sum(total <$(=GetObjectDimension(0))> [SALES_EUR]))

Both Sum expressions work on their own without the IF statement.

[rubenmarin]

You have 2 consecutive commas, remove the one after >=2