Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Script

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

3 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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

Gysbert_Wassenaar

Add {<YEAR={2015}>} to each aggregation function. For example sum(Amount) would become sum( {<YEAR={2015}>} Amount)


talk is cheap, supply exceeds demand