5 Replies Latest reply: Mar 18, 2013 4:17 AM by Thorsten Schröder RSS

    Calculating with different Currency rates (sum in pivot)

    Thorsten Schröder

      Hello comunity,

       

      I need help with my currency problem:

      I have various currencies and I use a currency/rate table (ECB) to get the right values at the right day.

      I now need to multiply my Amount field which also has a Curreny field with the right Currency and Date. This all works fine.

       

      The problem I have is that when my currency is EUR I don't have any values in my ECB rate table, so I'll just use this if statement in my vEurAmount variable:

       

      if(Currency <>'EUR', sum(Amount / (1 * ECBrate)),sum(Amount))
      

       

      In my straight table I can set the total mode to "Sum of all rows" but I can't use this in my pivot table.

      So I tried this expression:

      =sum(aggr(sum($(vEurAmount)) 
      

      which won't return anything.

      I've tried other combinations but I can't get the syntax right.

       

      Hope you can help me to set up the right expression.

       

      BTW, is the variable I defined the right way to do this? I've also removed the sum function in the variable but no luck either.

       

      thank you.