Hi Everyone - hope you are having a good day.
Some of my data is collected in South African Rand (ZAR) and i need to convert it into Euros (EUR). The data comes in once a month everyone month and so there is a new FX rate that needs to be used each month to convert the data. In order to convert the data into EURs i need to use a weighted average of the FX rate as there are multiple contributing factors, which means the previous months FX rates need to also be taken into consideration.
I have attached an excel spreadsheet with an example of the data, the white columns is raw data that would be given to me and the yellow column is the column i am trying to calculate.
In Qliksense i have created the following formula to try and replicate this yellow column:
sum([Net Loss due to non Performing Loan (ZAR)])/(RangeSum(Above(Total Sum([FX Forward Rate]*[Expected Collection (ZAR)]),0,RowNo(TOTAL)))/Rangesum(Above(Total Sum([Expected Collection (ZAR)]), 0, RowNo(Total))))
My issue however is that the above calculation gives me answers which are roughly half of the correct values, e.g. instead of 518.74 the value is 258.96.
I have figured out that the issue arises in the bold section of the calculation above, where my aim was to do a cumulative sum product. The bold section of the calculation is double what i would have expected which is what is causing the halving in the final answer.
Any ideas on how to fix this?
Thanks so much in advance,
Dan
