Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
the below script shows zero result, could you please advise
=
sum({$<YEAR = {$(=max(YEAR))},MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))"}>}
sum(if([PRODUCTION LOCATION]=[SALES LOCATION],
([PRODUCTION FOB QTY]+([PRODUCTION SUB QTY]*(-1)))*([FOB WITHOUT COMMISSION]/[PACK QTY]),
0)
+
sum([PRODUCTION SUB QTY]*[SUB CM])))
If you nest aggregation functions like sum then you need to use the aggr function to specify over which dimensions the inner sum should be aggregated.
=sum({$<YEAR = {$(=max(YEAR))},MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))"}>}
aggr(sum(if([PRODUCTION LOCATION]=[SALES LOCATION],
([PRODUCTION FOB QTY]+([PRODUCTION SUB QTY]*(-1)))*([FOB WITHOUT COMMISSION]/[PACK QTY]),0)
+ aggr(sum([PRODUCTION SUB QTY]*[SUB CM]),DimP,DimQ,...,DimY),DimA,DimB,...,DimX))
Hi,
i think i have not communicated properly, actual issue is how do i calculate only for YEAR=2015
(sum(if([PRODUCTION LOCATION]=[SALES LOCATION],
([PRODUCTION FOB QTY]+([PRODUCTION SUB QTY]*(-1)))
*([FOB WITHOUT COMMISSION]/[PACK QTY]),0))
+
sum([PRODUCTION SUB QTY]*[SUB CM])))
Add {<YEAR={2015}>} to each aggregation function. For example sum(Amount) would become sum( {<YEAR={2015}>} Amount)