Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
hramesh1
New Contributor II

Exchange Rate Variance

Hi, 

I am trying to develop a dashboard for my team, who works to identify the variance due to exchange movement. 

1. Exchange rates are issued monthly once, original version is in sheet "Exchange" and i have customized to fit the dashboard in "Customized sheet". Each are denoted by "Monthly issue number" which is unique. For e.g 864 is Dec'18 issue number

2. Product details, we have multiple products lined up against each region. Each region has its own cost and buying from different suppliers across multiple currencies. 

I will be given with each product's cost. For e.g Product X with cost of 10,000 (USD) and with exposure percentage across different currencies for e.g  20% in CAD, 10% in GBP and 40% in CNY and 15 in EUR and remaining 15 in USD and the product Launching Year e.g 2018, 2019, 2020 etc. 

My requirement is to understand the Exchange movement of a product between different time frame. Say for e.g  Jan 2017 vs Nov 2018, this should give the selection option for user to select freely their comparison time frame. 

 

 

 

 

 

 

1 Reply
hramesh1
New Contributor II

Re: Exchange Rate Variance

Apologies, forgot to attach the output details which i have calculated in excel (Highlighted in green), With this, i have also attached the sample qvw file for your references

i assume that you dint understand my questions which i have sent you yesterday. I will try to elaborate bit more on the requirement.

Product NameRegionMat Cost in USDCurr.CdeExposure (%)Base Issue #Job1 YearAmount in Currency Split (USD)Base Exc RateAmt in Local CurrencyComparative IssueComparative ExcComparative Amount in Currency Split (USD)Variance
XNA $              10,000.00EUR29%8462018 $                                       2,883.95                    0.87                           2,511.91851                        0.85                                                                 2,964.26         80.31
YNA $              20,000.00EUR30%8562018 $                                       5,948.89                    0.81                           4,803.16858                        0.83                                                                 5,753.37       (195.52)

We will be given only with Material cost and Exposure % we are buying in different currency. In the above case, i have selected Euro currency as example and 29% of product X material cost is bought in Euro currency and exchange rate for #846 issue month is at 0.87 and when i compare the same with issue # 851 which is 0.85 this gives the exchange variance of $80.

The part where i am struck is, both issues (base & Comparative ) has to be given as selection option for users to determine their comparison period.

Please help me to find a solution....Thanks for the support!!Capture.PNG