Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
(2) Pricing Model - setup as variables in order to allow on-the-fly-simulation reuse in different objects (charts, tables)
(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)))
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))))
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))))
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))))
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))))