Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have a the below expression the calculate the sales for branches with a condition to except WEB sales for 7 branches
the values are coming correctly but the totals are not correct i have to use a Pivot table
if(Match(BRANCH,'0001','0021','0031','0041','0051','0061','0071'),
SUM(AGGR((Sum({<MONTH={'$(v1MonthYear)'},WEB-={1}>} SALE)/Sum({<MONTH={'$(v1MonthYear)'}>} WORKINGDAYS)),
BRANCH)),
SUM(AGGR(
(Sum({<MONTH={'$(v1MonthYear)'}>} SALE)/Sum({<MONTH={'$(v1MonthYear)'}>} WORKINGDAYS))
,AREA,BRANCH))
)
Please Suggest
Try another Aggr()
Sum(Aggr(If(Match(BRANCH,'0001','0021','0031','0041','0051','0061','0071'),
SUM(AGGR((Sum({<MONTH={'$(v1MonthYear)'},WEB-={1}>} SALE)/Sum({<MONTH={'$(v1MonthYear)'}>} WORKINGDAYS)),
BRANCH)),
SUM(AGGR(
(Sum({<MONTH={'$(v1MonthYear)'}>} SALE)/Sum({<MONTH={'$(v1MonthYear)'}>} WORKINGDAYS))
,AREA,BRANCH))
), YourChartDimensions))
Also if this is in the straight table, make use of Total Mode rather than Aggr() function.
Try another Aggr()
Sum(Aggr(If(Match(BRANCH,'0001','0021','0031','0041','0051','0061','0071'),
SUM(AGGR((Sum({<MONTH={'$(v1MonthYear)'},WEB-={1}>} SALE)/Sum({<MONTH={'$(v1MonthYear)'}>} WORKINGDAYS)),
BRANCH)),
SUM(AGGR(
(Sum({<MONTH={'$(v1MonthYear)'}>} SALE)/Sum({<MONTH={'$(v1MonthYear)'}>} WORKINGDAYS))
,AREA,BRANCH))
), YourChartDimensions))
Also if this is in the straight table, make use of Total Mode rather than Aggr() function.
HI Sunny
Thanks it worked
I believe, Partial sums simply sum the rows.