Chart Level Scripting Use Case - Table Totals Row

[Jim_Ogilvie]

Hi all,

I've been successfully using some Chart Scripting to create new columns in a straight table.

However, Qlik Sense doesn't calculate for the Totals row of the table.

Does anyone know how we can access the total row in the same way we access the detail row of the underlying dataset?

Thanks

Jim

[henrikalmen]

Can you give an example of what your loaded table contains, what you are getting and what you want instead? You are mentioning the "total row", is that in your underlying dataset or do you mean the calculation total?

[Jim_Ogilvie]

Thanks for your reply.

Yes, the Calculation Total row. Is it possible to access that programmatically via the chart script?

I have a situation with a PVM (Price/Volume/Mix) formula, which means it returns 0 (zero) in the Calculation Total row - so wanted to override the default behaviour with a chart script.

[henrikalmen]

You can't override the total calculation of a formula in a chart. It gives you the total of the expression. Possibly, you could change the expression so that the total will become what you want it to be but I can't make suggestions without seeing example of the data and the calculation.

[Jim_Ogilvie]

OK - understood.

That's a shame. There is no other way around this, unfortunately. 

The behaviour I'm trying to force is replicating something my boss has built in Excel. He has a set of PVM calculations which work across rows, but then in the total row, the formula is changed in a couple of cells to refer to the total of the column, rather than simply across rows.

I can force the Qlik table total to display the total by changing the total calculation to "Sum" for one of the columns, but for the cell that calculates a %, I need to refer to that "Summed" value. Using "Sum" in this column isn't appropriate and if I leave it on "Auto" - in the background though Qlik reverts to applying the formula across the Total Calculation row, but doesn't use the "Summed" value, it uses the expression as per all the rows below.

Thanks for your time

[henrikalmen]

There are ways to force what is calculated with in a field, with set analysis and/or the aggr() function and/or the TOTAL qualifier.

There seems to be a couple of qlik community threads on PVM analysis, e.g. https://community.qlik.com/t5/QlikView-App-Dev/Price-Volume-Mix-effect-analysis-PVM/td-p/297614 Perhaps you can figure something out via other existing conversations. Good luck!