Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have to switch between Local Currency, EUR and USD. I have created the dimension as Cost_LC, Cost_USD, Cost_EUR.
I need to create a variable for currency and use that in KPI's and graphs.
I also have a button for the currencies. I need to switch between the 3 KPIs.
How to create a variable? and how to use it in KPIs.
Please need some as I am new to qlik.
Thanks.
You would create the variable as containing whichever currency you want to be the default, and use the Variable Input object to allow the user to change it.
You can create a variable either from the script (using a Set or Let statement) or from the Variable Editor in the bottom left corner of Edit Mode. Controlling the variable inputs using the Variable Input object is pretty self-explanatory and covered in the documentation if necessary.
Your eventual formulas would be something like Sum(Pick(Match(vCurrency,'LC','USD','EUR'),Cost_LC, Cost_USD,Cost_EUR)) or if preferred, you could write this out with a pair of if() statements instead.
Hi @Or ,
Thanks a lot for the quick reply. .
Can I get some help in writing the variable in the variable editor and use that in expression.
For example: I have this expression, I need to modify it using the variable.
SUM({$<YearMonthDay={$(vL.MaxYearMonthDay)}, Status={'no'}>} Aggr(Sum(DISTINCT Cost_EUR), [%ID]))
I need to use the variable in this expression and something similar in the graphs where I should switch between the other currencies.
How do I modify it?
Thanks!
You would use the exact formula from my previous post (assuming your variable was named vCurrency).
Could You please elaborate. I am stuck.
Should I create a variable as,
vCurrency = 'EUR','USD','LC' ?
You would create the variable as containing whichever currency you want to be the default, and use the Variable Input object to allow the user to change it.
Hi,
I modified like this,
SUM({$<YearMonthDay={$(vL.MaxYearMonthDay)}, Status={'no'}>} Aggr(Sum(DISTINCT Cost_$(vCurrency)), [%ID]))
and vCurrency = 'EUR','USD','LC';
But no luck. Am I right?
No. If you want to write it that way you would need something like $(='Cost_' & vCurrency). I personally do not like using that method unless the list of possible variable values is too long for Pick/Match to be feasible, so I would personally not use that approach.
vCurrency should always have a single currency selected. It should not have three comma-separated "options".
What should be defined in vCurrency ?
Super. It worked 🙂 Thanks for guiding!