topic Re: Dynamic Total in Pivot Table in App Development

Dynamic Total in Pivot Table

Tim_G — Mon, 13 Nov 2023 11:15:45 GMT

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

Re: Dynamic Total in Pivot Table

rubenmarin — Mon, 13 Nov 2023 11:29:17 GMT

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])

Re: Dynamic Total in Pivot Table

Tim_G — Tue, 14 Nov 2023 09:46:33 GMT

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

Re: Dynamic Total in Pivot Table

rubenmarin — Tue, 14 Nov 2023 10:35:47 GMT

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?

Re: Dynamic Total in Pivot Table

Tim_G — Tue, 14 Nov 2023 14:47:57 GMT

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.)

Re: Dynamic Total in Pivot Table

rubenmarin — Sat, 18 Nov 2023 18:23:40 GMT

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])
)

Re: Dynamic Total in Pivot Table

Tim_G — Wed, 22 Nov 2023 13:52:26 GMT

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?

Re: Dynamic Total in Pivot Table

rubenmarin — Wed, 22 Nov 2023 14:13:00 GMT

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.

Re: Dynamic Total in Pivot Table

Tim_G — Wed, 22 Nov 2023 14:17:33 GMT

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.

Re: Dynamic Total in Pivot Table

rubenmarin — Wed, 22 Nov 2023 14:19:39 GMT

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

Re: Dynamic Total in Pivot Table

Tim_G — Wed, 22 Nov 2023 14:21:57 GMT

Oof, I missed that. So sorry! 😥