Calculate Total For 2 Rows in Another Row in Pivot Table

MCFH93 — Wed, 10 Jul 2024 08:31:12 GMT

Hi, I would like to display the total for F and G in H row as shown below. Below are the dimensions and expressions used in the pivot table. Can advise on how can I achieve this. Thank you.

Current Pivot Table

Expected Pivot Table

Dimension for Pivot Table

(1) SM2_SOURCE

(2) =IF(DATE(YEAREND(DATE(SM2_DATE, 'DD/MM/YYYY'), 0, 4), 'YYYY') >= YEARSTART(TODAY(), -2, 4), 'FY ' & DATE(YEAREND(DATE(SM2_DATE, 'DD/MM/YYYY'), 0, 4), 'YYYY'))

Expression for Pivot Table

(1) tCO₂e = SUM(AGGR(IF(SUM(SM2_VALUE) > 0, (SUM(SM2_VALUE) * SM2_CONVERSION_FACTOR) / 1000), SM2_SOURCE, SM2_DATE))

(2) MWh = ROUND(SUM(SM2_VALUE) / 1000)

Re: Calculate Total For 2 Rows in Another Row in Pivot Table

marcus_sommer — Wed, 10 Jul 2024 12:20:36 GMT

You may just query the source and using there the wanted calculation, like:

if(Source = 'x', Expr1, if(Source = 'y', Expr2, ..., ExprX))

Beside this you could add some upper dimension-level and then using their partial sums and/or a query on the dimensionality() to branch within different calculations.

Independent from this I suggest to replace the calculated date-dimension with native fields from an appropriate calendar and also the check if the calculation of tCO₂e requires really an aggr() and an if-loop.

topic Calculate Total For 2 Rows in Another Row in Pivot Table in QlikView

Calculate Total For 2 Rows in Another Row in Pivot Table

Re: Calculate Total For 2 Rows in Another Row in Pivot Table