Qlik Community

Qlik Sense App Development

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

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor III

Currency Conversion using Climber Filter selection

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?

4 Replies
Highlighted
Partner
Partner

Re: Currency Conversion using Climber Filter selection

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))

Highlighted
Valued Contributor

Re: Currency Conversion using Climber Filter selection

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

Re: Currency Conversion using Climber Filter selection

Here is the example QVF hope it resolves your query

Highlighted
New Contributor III

Re: Currency Conversion using Climber Filter selection

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)))

2)

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!