1 Reply Latest reply: Oct 22, 2014 2:02 PM by HECTOR GARCIA RSS

    Set Analysis on row level

      Dear QlikCommunity

       

      I setup an app to do pricing simulations. For tracking/bugfixing reasons, I need to do the calculations on row level. The calculation requires nested ifs (see below example). The example is heavily simplified vs. the oringinal one, where I have more nesting and about 10m records.

       

      The question is, wheter it makes sense / is possible to put such a calculation into set analysis on row level.

       

      (i) The calculation on row level sits in a variable (vSalesPriceSimIf) --> works fine:

       

      if(TrxCat = 'Sales',

      NumMin (vSalesMax, NumMax (vSalesMin,

      (if (QuantCl = 'be500', SalesQuant * vSalesBe500 / 10000,

      if (QuantCl = 'be1000', SalesQuant * vSalesBe1000 / 10000,

      if (QuantCl = 'ab1000', SalesQuant * vSalesAb1000 / 10000)))))))

       

       

      (ii) From the variable I create different views / aggregations --> works fine

      sum($(vSalesPriceSimIf))

       

       

      (iii) With Set Analysis, I could reach the following, also put into a variable (vSalesPriceSimSet) --> seems to work only with Sum:

       

      = (sum ({$ <TrxCat = {'Sales'}, SalesQuantCl = {'be500'} >} NumMin (vSalesMax, NumMax (vSalesMin, SalesQuant * vSalesBe500 / 10000)))

      + sum ({$ <TrxCat = {'Sales'}, SalesQuantCl = {'be1000'} >} NumMin (vSalesMax, NumMax (vSalesMin, SalesQuant * vSalesBe1000 / 10000)))

      + sum ({$ <TrxCat = {'Sales'}, SalesQuantCl = {'ab1000'} >} NumMin (vSalesMax, NumMax (vSalesMin, SalesQuant * vSalesAb1000 / 10000))))

       

      --> The question is, wheter it is possible withouth the Sum, respectively wheter it would bring any advantage.

       

      Thanks, Michael