topic Re: conditional expression in QlikView

conditional expression

Mon, 07 Dec 2015 15:22:55 GMT

Hi Experts,

i have the following expression.

(Sum({<MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}Quantity)

Sum({<MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}units))

in my sales table, i have around 100 branches. from branch no. 1 to branch no. 100

my requirement is to calculate the above expression with condition.

if my branch no. is 2, then i need to do the above calculation for branch no. 2 & 7 together.

i.e., if branch =2,

then i have to add the values of branch 2 & 7, else the actual branch values

i tried the below expression

if(Branch='2',

(Sum({<Branch={'2','7'}, MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}Quantity)

Sum({<Branch={'2','7'},MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}units))

10,

(Sum({<MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}Quantity)

Sum({<MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}units))

)

but this is not working fine.

please help me to correct the expression

Re: conditional expression

settu_periasamy — Mon, 07 Dec 2015 15:29:58 GMT

Hi,

Hope your normal set expression is working fine.

May be try it opposite way..like

If (Branch <>2, yoursecondexpression,firstexpression)

Re: conditional expression

Mon, 07 Dec 2015 15:44:15 GMT

No,

its not working.

still the same

pls give some other suggestions

Re: conditional expression

sfatoux72 — Mon, 07 Dec 2015 16:04:55 GMT

Hi Prrajendran,

Try like that:

if(Branch=2,

(Sum({<Branch={2,7}, MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>} TOTAL Quantity)

Sum({<Branch={2,7},MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>} TOTAL units))

10,

(Sum({<MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}Quantity)

Sum({<MonthYear =,Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}units))

)

Re: conditional expression

Peter_Cammaert — Mon, 07 Dec 2015 16:26:19 GMT

Seems logical to me. When the expression engine arrives at the row with Branch=2, the entire data set has been reduced to lines that only have Branch = 2. Branch = 2,7 won't change it because QlikView makes a selection for Branch=2 in the resulting data set of the set expression (Branch = 2,7), not before. Remember that set analysis is applied before the object is recalculated. And all expressions are evaluated during recalculation.

Adding the TOTAL keyword between the set specification and the Quantity or Units fields will solved this conflict. See document in attachment for an example.

Peter

Re: conditional expression

Tue, 08 Dec 2015 09:01:01 GMT

thanks to all