QLIK EXPRESSION

[RobinTMavhu]

PLEASE HELP: My expression is only working for a table but not a KPI. The idea is add up closing stock per material from various stores(for the latest date) and then divide by maximum stock level (AVERAGE_STOCK*6). The expression and respective screenshots as per below:

=(
Sum(
Aggr(
If(TO_DATE = Max(TOTAL TO_DATE), Sum(CLOSING_STOCK)),
TO_DATE,
MATERIAL
)
)
)
/
(AVERAGE_STOCK*6)

[FedericoDellAcqua]

What if you remove the MATERIAL from the aggregation inside the KPI? 

[RobinTMavhu]

No luck still.

[FedericoDellAcqua · Accepted solution]

Ok, and why not including the /(AVERAGE_STOCK*6) inside the aggregation?

[RobinTMavhu]

Thank you so much probably there is bit of rounding issues but im going with your suggestion. 

AVG(
Aggr(
If(TO_DATE = Max(TOTAL TO_DATE), Sum(CLOSING_STOCK) / (AVERAGE_STOCK*6)),
TO_DATE,
MATERIAL
)
)