topic Re: Retrieving SubTotals from a qHyperCube in DATA_MODE_PIVOT in Integration, Extension & APIs

Retrieving SubTotals from a qHyperCube in DATA_MODE_PIVOT

simondachstr — Mon, 13 Jun 2016 16:10:14 GMT

Hi,

We are in the process of developing an extension object similar to a classic pivot table utilizing Hypercube's qMode property called DATA_MODE_PIVOT (Pivot Table data representation).

When the pivot table is expanded, we still need to display the sub totals (Think of a classic QlikView Pivottable with subtotals), but we have no idea where to get the aggregated data from.

On the Straight Table there's the qGrandTotalRow property which returns the total row for the whole column - we need the same but for the SubTotals which ideally gets updated on the ExpandLeft/ExpandTop method.

Thanks,

Martin

Re: Retrieving SubTotals from a qHyperCube in DATA_MODE_PIVOT

Alexander_Thor — Mon, 13 Jun 2016 18:42:41 GMT

Hey Martin,

If you are in Pivot/Stacked mode then you are looking for the cell that contains qType = T.

This is the corresponding struct: http://help.qlik.com/en-US/sense-developer/2.2/Subsystems/EngineAPI/Content/Structs/NxPivotValuePoint.htm

Re: Retrieving SubTotals from a qHyperCube in DATA_MODE_PIVOT

simondachstr — Tue, 14 Jun 2016 08:41:11 GMT

Thanks akl‌

Is the qType = T cell returned automatically or do I need to enable it in the qHyperCubeDef properties first, similar to qSuppressZero/qSuppressMissing ?

Re: Retrieving SubTotals from a qHyperCube in DATA_MODE_PIVOT

simondachstr — Tue, 14 Jun 2016 13:25:20 GMT

Using qTotalMode did the trick

Re: Retrieving SubTotals from a qHyperCube in DATA_MODE_PIVOT

chriscammers — Tue, 11 Oct 2016 15:51:47 GMT

Where did you find the qTotalMode? is it a property of the entire cube or individual fields?

I cannot seem to find any references to it in the help.

Thanks

Re: Retrieving SubTotals from a qHyperCube in DATA_MODE_PIVOT

Alexander_Thor — Tue, 11 Oct 2016 16:36:21 GMT

It's part of the OtherTotalSpecProp struct that sets via qOtherTotalSpec on the qDimensions.

You can it linked here (sadly anchor links dosen't seem to work so scroll down) http://help.qlik.com/en-US/sense-developer/3.1/Subsystems/EngineAPI/Content/GenericObject/PropertyLevel/HyperCubeDef.htm

Or at OtherTotalSpecProp ‒ Qlik Sense

Re: Retrieving SubTotals from a qHyperCube in DATA_MODE_PIVOT

chriscammers — Wed, 12 Oct 2016 12:08:21 GMT

Thanks Alexander,

Your help is much appreciated

Chris

Re: Retrieving SubTotals from a qHyperCube in DATA_MODE_PIVOT

felisberto_joao — Wed, 14 Feb 2018 19:26:23 GMT

Hello Martin,

I need your help! Trying to get the qGrandTotalRow... Did you find a solution for that? I have checked the help and qlick comunity but not everibody use the Table Pivot extension with multiples dimensions.

I create a total row but in some case like sum of % it doesn´t well. Can you help me how to show the qGrandTotalRow?

Thanks.

Best regards.

Felisberto

Re: Retrieving SubTotals from a qHyperCube in DATA_MODE_PIVOT

ygfletch — Thu, 12 Mar 2020 16:28:26 GMT

Hi Alex,

Sorry to highjack an old post but I'm having some real trouble with setting this property. I have in my dimensions definition for the extension included the qOtherTotalSpec.qTotalMode property and set it to the appropriate string via the property panel. I have also set the qMode to P. However I can't get the totals to show in the qData property on the qPivotDataPages, all the values show as type V.

Please tell me there's something obvious I'm doing wrong to make this property take affect!

{ type: "items", component: "accordion", items: { dimensions: { uses: "dimensions", min: 0, max: 11, items: { pivotTotal: { type: "string", component: "switch", label: "Pivot Totals", ref: "qOtherTotalSpec.qTotalMode", options: [ {value: "TOTAL_OFF", label: "OFF"}, {value: "TOTAL_EXPR", label: "ON"} ], defaultValue: "TOTAL_OFF" } } } } }