Sum of rows in pivot table

[#1]

Hi

I have an expression in a pivot table that gives me the correct value per row but the chart total does not add up as it's using expression total rather than sum of rows.  I know this is caused by the last line of the expression below ie the max() part, but I can't figure out how to rewrite this to work correctly.  My report has a Reporting Currency and Reporting Units so the user can decide to display the values in the report in any currency or unit (eg USD per Metric Ton or GBP per kilogram) and I need to convert all values to those units.  The kicker is that the user can also override the spot FX rate in the report and if they do I need to use that instead.  I've written 2 functions (IsValidRate and GetUSDRate) to help identify which rates to use.

So essentially what this expression is doing is taking the Cost Per Unit and then converting it to the reporting currency using either the monthly FX rate or the Spot FX rate.  If the spot FX rate is to be used then I need to check if it's been overridden and use that.  Lastly I need to convert it to the Reporting Units. 

=sum(
       [Cost Amount Per Unit]
       /
       if([%FX Currency Short Code]=[Cost Currency],
              if([Use Monthly FX Rates] = 1,
                     if($(IsValidRate([Cost Monthly USD Rate])) <> 0, [Cost Monthly USD Rate],
                           $(GetUSDRate([Override USD Rate],[Cost Spot USD Rate]))
                     )
                     ,
                     $(GetUSDRate([Override USD Rate],[Cost Spot USD Rate]))
              )
       )
       * RepCcyUSDRate
)
*
max(if([Cost Qty UOM]=[To UOM Code] and [From UOM Code]=RepUOM,[UOM Conversion Factor]))

Does anyone know how I could rewrite this to work correctly?

Thanks

Gregg.

[Anonymous]

Try taking your expression (Exp) and wrapping it in an aggr() function aggregating the values over your dimension(s):

sum(aggr(Exp, Dim1, Dim2...))

This way you will calculate each sum of the combinations of your dimension values, and for the total you will sum up all these pieces to simulate a "Sum of Rows" for the pivot table.

[#3]

Hi Johannes

Thanks for the suggestion.  Unfortunately when I add the aggregate function it returns zero.

I think I need to find a way to incorporate the if statement that is currently within the max function into the sum function but this doesn't seem possible.  Something like:

=sum(
       [Cost Amount Per Unit]
       /
       if([%FX Currency Short Code]=[Cost Currency],
              if([Use Monthly FX Rates] = 1,
                     if($(IsValidRate([Cost Monthly USD Rate])) <> 0,[Cost Monthly USD Rate],
                           $(GetUSDRate([Override USD Rate],[Cost Spot USD Rate]))
                     )
                     ,
                     $(GetUSDRate([Override USD Rate],[Cost Spot USD Rate]))
              )
       )
       * RepCcyUSDRate

       *

       if([Cost Qty UOM]=[To UOM Code] and [From UOM Code]=RepUOM,[UOM Conversion Factor])

)

But when I try this it returns zero as QV doesn't seem able to evaluate 2 disparate if statements within the one sum function.

Thanks

Gregg.

[#4 · Accepted solution]

Hi again

Just posting in case anyone runs into the same problem.  I played around with Johannes' suggestion above and managed to get it working using the aggr() funciton as below:

=sum(
       aggr(nodistinct
              max(
                     [Cost Amount Per Unit]
                     /
                     if([%FX Currency Short Code]=[Cost Currency],
                           if([Use Monthly FX Rates] = 1,
                                  if($(IsValidRate([Cost Monthly USD Rate])) <> 0, [Cost Monthly USD Rate],
                                         $(GetUSDRate([Override USD Rate],[Cost Spot USD Rate]))
                                  )
                                  ,
                                  $(GetUSDRate([Override USD Rate],[Cost Spot USD Rate]))
                           )
                     )
                     *
                     RepCcyUSDRate
                     *
                     if([Cost Qty UOM]=[To UOM Code] and [From UOM Code]=RepUOM,[UOM Conversion Factor])
              )
              ,[%Cost ID]
       )
)

Thanks

Gregg.