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