3 Replies Latest reply: Jul 5, 2018 6:06 PM by Prakhar Khandelwal RSS

    Nested If else in SET ANALYSIS

    Prakhar Khandelwal

      Hello Guyz,

      I am having a problem with converting this nested If else statement in SET analysis,

       

      sum(  DISTINCT


      If(Temp_Target='INR',

          IF(Temp_Base ='INR',Temp_TotalAmount,

             IF(Temp_Base='USD',Temp_TotalAmount*(65),

              Temp_TotalAmount*(3.62))),

      If(Temp_Target='AED',

          IF(Temp_Base ='INR',Temp_TotalAmount*(1/3.62),

             IF(Temp_Base='USD',Temp_TotalAmount*(65/3.62),

              Temp_TotalAmount)),

      If(Temp_Target='USD',

          IF(Temp_Base ='INR',Temp_TotalAmount*(1/65),

             IF(Temp_Base='USD',Temp_TotalAmount,

              Temp_TotalAmount*(3.62/65))),

         )

         )

         )

         )



      It's just a Filter Temp_Target,

      and likewise it would convert the TotalAmount.

      Please Let me knw how to Solve this particular!

       

      Regards

      Prakhar

        • Re: Nested If else in SET ANALYSIS
          Petter Skjolden

          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.

           

           

          DateAmountCurrency
          .......100USD
          .......120AED
          ......5000INR

           

           

          CurrencyTargetRate
          INRINR1
          INRUSD65
          INRAED3.62
          AEDAED1
          AEDINR
          0,276243094
          AEDUSD
          17,9558011
          USDUSD1
          USDINR
          0,015384615
          USDAED
          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:

           

           

          TargetAmount:   expression is      =Sum( Amount * Rate )
          INRxxxx
          AEDyyy
          USDzzz