Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi,
Hope your normal set expression is working fine.
May be try it opposite way..like
If (Branch <>2, yoursecondexpression,firstexpression)
No,
its not working.
still the same
pls give some other suggestions
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
)
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
thanks to all