
Re: Nested If else in SET ANALYSIS
Petter Skjolden Jul 5, 2018 2:39 PM (in response to Prakhar Khandelwal)It's a rather messy way of doing currency conversion  but I don't know the context so here is another messy way of doing it with Set Expressions:
Sum( {<Temp_Target={'INR'},Temp_Base={'INR'}>} Temp_TotalAmount)
+ Sum( {<Temp_Target={'INR'},Temp_Base={'USD'}>} Temp_TotalAmount)*65
+ Sum( {<Temp_Target={'INR'},Temp_Base={'AED'}>} Temp_TotalAmount)*3.62
+ Sum( {<Temp_Target={'AED'},Temp_Base={'INR'}>} Temp_TotalAmount)/3.62
+ Sum( {<Temp_Target={'AED'},Temp_Base={'USD'}>} Temp_TotalAmount)*65/3.62
+ Sum( {<Temp_Target={'AED'},Temp_Base={'AED'}>} Temp_TotalAmount)
+ Sum( {<Temp_Target={'USD'},Temp_Base={'INR'}>} Temp_TotalAmount)/65
+ Sum( {<Temp_Target={'USD'},Temp_Base={'USD'}>} Temp_TotalAmount)
+ Sum( {<Temp_Target={'USD'},Temp_Base={'AED'}>} Temp_TotalAmount)*3.62/65
Note that this is a calculation that needs to have one and only one Temp_Target selected or in a chart where one of the dimensions is Temp_Target or you will end up adding sums from different currencies.
TIP:
Usually currency conversion are better done by using the associative model in Qlik and create a currency conversion table. That way the key between the currency table and the fact table where the amounts live is the Target and Base which has to be named the same for the association to work.
Date Amount Currency ....... 100 USD ....... 120 AED ...... 5000 INR Currency Target Rate INR INR 1 INR USD 65 INR AED 3.62 AED AED 1 AED INR 0,276243094 AED USD 17,9558011 USD USD 1 USD INR 0,015384615 USD AED 0,055692308 Here the Currency field will be a key field connecting the tables or associating them as it is called in Qlik terminology.
Sum( {<Target={'INR'}>} Amount*Rate )
Will very efficiently caluculate the Sum of Amounts converted into INR whichever the base currency is. You will simply change the set element to the target currency you want to have calculated.
If you have a table like this in your sheet you don't even need to have the set expression since one of the dimensions is Target:
Target Amount: expression is =Sum( Amount * Rate ) INR xxxx AED yyy USD zzz 
Re: Nested If else in SET ANALYSIS
Prakhar Khandelwal Jul 5, 2018 6:06 PM (in response to Petter Skjolden )Thank You Sir,
It was the Required Answer!
And Yes, I tried the same
sum({<Target={'INR'}>}Total*CRate)
But as It was not linked in the Currency fashion instead linked with the ID pattern that's why i made a Hanging table for this where all the records are connected with the Base and the Target.
Thanking you again!
Regards
Prakhar Khandelwal

Re: Nested If else in SET ANALYSIS
Petter Skjolden Jul 5, 2018 6:08 PM (in response to Prakhar Khandelwal)You're welcome  happy to help

