topic Re: Set Analysis nested If/Else in QlikView

Set Analysis nested If/Else

Thu, 27 Dec 2012 09:38:43 GMT

Dear QlikCommunity

I'm relatively new to Qlikview and am struggling with the set analysis. I would like to move a nested if statement into set analysis, sitting in a variable. Usecase is to simulate pricing models based on Sales transactions. The pricing is defined with different quantity tiers and minimums and maxiums.

Many thanks in advance, Michael (I use pe edition)

(1) Database - Transactions with

Mastercategory (TrxCat) --> I would need to apply the pricing model only on 'Sales'
Quantityclusters (QuantCl):
- 'be500' --> transactions with quanty below 500
- 'be1000' --> transactions with quanty between 500 and 1000
- 'ab1000' --> transactions with quanty above 1000
Some other data

(2) Pricing Model - setup as variables in order to allow on-the-fly-simulation reuse in different objects (charts, tables)

Bps price for every QuantCl:
- 'vSalesBe500' --> price in bps that applys to this QuantCl
- 'vSalesBe1000' --> price in bps that applys to this QuantCl
- 'vSalesAb1000' --> price in bps that applys to this QuantCl
The above prices are overruled by:
- 'vSalesMin' --> a minimum price (floor)
- 'vSalesMax' --> a maxium price (cap

(3) With IF, I use the following - setup in a variable:

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)))))))

(4) now, the question is, how I could achieve the same with a set analysis in a variable

Tried something like this, but I cannot figure out how to setup the nested QuantCl conditions:

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

Re: Set Analysis nested If/Else

Mon, 31 Dec 2012 09:23:10 GMT

I could figure it out:

= (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))))

Re: Set Analysis nested If/Else

Mon, 31 Dec 2012 09:23:27 GMT

I could figure it out:

= (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))))

Re: Set Analysis nested If/Else

Mon, 31 Dec 2012 09:23:47 GMT

I could figure it out:

= (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))))

Re: Set Analysis nested If/Else

Mon, 31 Dec 2012 09:24:07 GMT

I could figure it out:

= (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))))