Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have an expression that goes like this
if(Allow = 'Y', SUM({$<DirAmount={'-1'}, Contract = {'1'}>} POAmount + ConAmount),
if(Allow <> 'Y' and Contract = '1',
SUM({$< DirAmount={'-1'}, Enddate = {'>=$(vEndDate)'}>} POAmount + ConAmount), 0))
I am getting the correct values when displaying this in a straight table with checking the Sum of rows checkbox or when exporting to excel.
How can I display the sum of row values in a pivot table ? Or even display it in a straight table by checking the Expressions Total option.
Thanks,
Umar
You can do this using advanced aggregation (aggr() function). There is a chapter in the Help on how to use aggr() function to emulate sum-of-rows in a pivot table.
Basically it looks like:
= sum(
aggr(
if(Allow = 'Y', SUM({$<DirAmount={'-1'}, Contract = {'1'}>} POAmount + ConAmount),
if(Allow <> 'Y' and Contract = '1',
SUM({$< DirAmount={'-1'}, Enddate = {'>=$(vEndDate)'}>} POAmount + ConAmount), 0))
,YourChartDim1, YourChartDim2)
)
You need to replace YourChartDim1 etc. with all your chart dimensions (fields only!).
You can do this using advanced aggregation (aggr() function). There is a chapter in the Help on how to use aggr() function to emulate sum-of-rows in a pivot table.
Basically it looks like:
= sum(
aggr(
if(Allow = 'Y', SUM({$<DirAmount={'-1'}, Contract = {'1'}>} POAmount + ConAmount),
if(Allow <> 'Y' and Contract = '1',
SUM({$< DirAmount={'-1'}, Enddate = {'>=$(vEndDate)'}>} POAmount + ConAmount), 0))
,YourChartDim1, YourChartDim2)
)
You need to replace YourChartDim1 etc. with all your chart dimensions (fields only!).
Great ! Thank you Sir.
Regards,
Umar