Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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.