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

# 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

• ###### Re: Set Analysis on row level

do you have a qlik document with sample data that refelect the challenge??