Currency Conversion using Climber Filter selection

[deborahs]

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?

[dplr-rn]

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

[ajaykakkar93]

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

[ajaykakkar93]

Here is the example QVF hope it resolves your query

[deborahs]

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!