Set Analysis on row level

Mon, 31 Dec 2012 09:49:32 GMT

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

hectorgarcia — Wed, 22 Oct 2014 18:02:09 GMT

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

topic Set Analysis on row level in QlikView

Set Analysis on row level

Re: Set Analysis on row level