conditional expression

[#1]

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

/

10

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

/

10

)

but this is not working fine.

please help me to correct the expression

[settu_periasamy]

Hi,

Hope your normal set expression is working fine.

May be try it opposite way..like

If (Branch <>2, yoursecondexpression,firstexpression)

[#3]

No,

its not working.

still the same

pls give some other suggestions

[sfatoux72]

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

/

10

)

[Peter_Cammaert · Accepted solution]

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

[#6]

thanks to all