topic Re: Currency Conversion using Climber Filter selection in App Development

Currency Conversion using Climber Filter selection

deborahs — Fri, 17 May 2019 14:29:10 GMT

Hello Qlik Gods, I have an app in which all values are in USD. I have used climber filter extension to create a list of four fields which are the currencies and inline loaded them.I have cost price, sell price and net for which im using if(Selection='ABC',Sum(CostPrice(ABC))) ,

if(Selection='XYZ',Sum(CostPrice(XYZ)))

where ABC and XYZ are currency codes and Cost Price(ABC) are a field in the table.

So the graph works but I have buttons which shoes Quarterly, YTD, MTD and these are used to change the dimension dynamically! So my currency conversions dont reflect changes with these bcoz i think once the if condition is fulfilled, it wont change it! Any tips to go about it?

Re: Currency Conversion using Climber Filter selection

dplr-rn — Fri, 17 May 2019 15:32:52 GMT

can you share sample app for us to test and check.
Another way to do this instead of if(Selection='XYZ',Sum(CostPrice(XYZ)))
is to use a variable like below
Sum( $(vMeasureColumn))

Re: Currency Conversion using Climber Filter selection

ajaykakkar93 — Sat, 18 May 2019 02:39:55 GMT

I have a example ready I'll share you that u can take a look

Re: Currency Conversion using Climber Filter selection

ajaykakkar93 — Sat, 18 May 2019 03:11:42 GMT

Here is the example QVF hope it resolves your query

Re: Currency Conversion using Climber Filter selection

deborahs — Mon, 20 May 2019 15:00:02 GMT

Thank you for your time. This is kind of similar but let me give you some more context.

The data is of the format you showed and I have dynamic dimension and measure. Dynamic dimension has four options and the expression is

=$(=Pick(Match($(vTime),1,2,3,4),
'if([ABC.Year_Trade_Date]=Year(Quarterend(Today())),[ABC.Client Services])',
'if(QuarterEnd([ABC.TradeDate])=QuarterEnd(Today()),[ABC.Client Services])',
'if(QuarterEnd([ABC.TradeDate])<QuarterEnd(Today()) and QuarterEnd([ABC.TradeDate])>=QuarterEnd(Today(),-4),[ABC.Client Services])',
'if(QuarterEnd([ABC.TradeDate])=QuarterEnd(Today(),-1),[ABC.Client Services])'

))

and expression for measure is =$(=Pick(Match($(vMeasure),1,2,3,4),
vNet,
vInflow,
vOutflow,vTransaction
))

So if you select YTD, a value would be assigned to vTime and that particular dimension would be considered and if you want to see Net, youll have to select that button and youll get that measure.

So i added Climber custom filter in which i have four fields, 'AUD', 'GBP', 'EUR' and 'SGD'. And the field name is selection. So I use

if(GetSelectedCount(Selection)>0,
if(Selection='AUD',
(SUM(if([CGL.Amount (AUD)]>0 AND [ABC.Year_Trade_Date]=Year(Quarterend(Today())), [ABC.Amount (AUD)]))),
if(Selection='GBP',
(SUM(if([ABC.Amount (GBP)]>0 AND [ABC.Year_Trade_Date]=Year(Quarterend(Today())), [ABC.Amount (GBP)]))),
if(Selection='SGD',
(SUM(if([CGL.Amount (SGD)]>0 AND [ABC.Year_Trade_Date]=Year(Quarterend(Today())), [ABC.Amount (SGD)]))),
if(Selection='EUR',
(SUM(if([CGL.Amount (EUR)]>0 AND [ABC.Year_Trade_Date]=Year(Quarterend(Today())), [ABC.Amount (EUR)]))))//,
)
)
), (SUM(if(CGL.Amount>0 AND [CGL.Year_Trade_Date]=Year(Quarterend(Today())), CGL.Amount)))
)

this expression for currency conversion.

This expression works well with straight tables with static measures but the ones in which there are dynamic measures, I have to change the expression. Ive tried many possibilities,

1) If((Match($(vMeasure,1) ,Pick(Match(Selection),'AUD','GBP','EUR','SGD'),
vNetAUD,vNetGBP,vNetEUR,vNetSGD)))

If((Match($(vMeasure,1) and num(getSelectedCount(Selection)<=1)),Pick(vNet)),
If((Match($(vMeasure,1) and Selection=’AUD’),Pick(vNetAUD)),
If((Match($(vMeasure,1) and Selection=’GBP’),Pick(vNetGBP)),
If((Match($(vMeasure,1) and Selection=’EUR’),Pick(vNetEUR)),
If((Match($(vMeasure,1) and Selection=’SGD’),Pick(vNetSGD)))

None of these work!