1 Reply Latest reply: Feb 6, 2017 7:26 AM by Court van de Lisdonk RSS

    Problems with currency

    Court van de Lisdonk



      I have a document where I have SalesInvoicelines with Company, Datalevel and LineAmount. This LineAmounts are divided in ActualAmount and BudgetAmount depending on DataLevel. I have also a currency-rate because the amount can be in another currency like USD.

      So in my script I have two calculations:

      if (DataLevel = 'Budget' and  SalesInvoiceLineNumber > 0,(LineAmount/DWHConsolitationCompCurrencyExchangeRateToCurrency),NULL()) as BudgetAmount,


      if (DataLevel = 'Actual' and ( SalesInvoiceLineNumber = 0 or  SalesInvoiceLineNumber > 12),(LineAmount/DWHConsolitationCompCurrencyExchangeRateToCurrency),NULL()) as ActualAmount,


      I'm also loading a currencytable with different currencies. In the script I have a tab where a load that qvd and create a currencytable


      On my Dashboard I have several tables with dimensions like Year, Month, Company etc. I use als also different expressions to calculate Actual and Budget.

      Some expressions are :

      = Sum({$ < Year = {$(= vCurrentYear)}, Month = {"<= $(=max(Month))"}, Quarter =, Month= > } ActualAmount)

      = Sum({$ < Year = {$(= max(Year))}> } ActualAmount)

      or simple = SUM(ActualAmount)


      I have a Multibox for choosing the currency. When I build in the ExchangeRate, like = Sum(ActualAmount * ExchangeRate) I have to divide it by 2 to get the right amount.


      My questions are:

      1. How can I get EUR as Default Currency and 1 as Default ExchangeRate

      2. Is my currency table correct created (tried to build in a intervalmatch)

      3. Why should I divide the amount by 2 to get the right amounts


      As example I have a little document uploaded where my currency  table is created and a little part of the InvoiceLines is loaded


      Thanks in advance



        • Re: Problems with currency
          Court van de Lisdonk



          Found the solution for my questions

          For question one: created an Inline Table with one record (Euro and ExchangeRate)

          For question two: there are many examples on this site and with some combining I got the right IntervalMatch for my problem

          For question three: there was a mistake in our Datawarehouse, after that was solved, my Problem was solved.


          SO: Always check you data and watch the Solutions given by the experts


          See you