total stock kpi

Oliver1 — Mon, 09 Dec 2019 20:07:58 GMT

Hello,

I need some help for a specific KPI, please!

I have something like that:

material id	warehouse id	stock [pieces]	date
1	a	100	9.12.2019
1	a	50	8.12.2019
1	b	100	8.12.2019
1	c	100	8.12.2019
2	a	100	8.12.2019

Now I want to calculate the total stock, but only for the latest date for each warehouse and material. So if there are 2 different stocks on a different date but in the same warehouse and for the same material, only the latest counts.

For this example, the result should be:
for material 1: 300
for material 2: 100
total: 400

I've tried something like that "sum(aggr(stock_pieces, material_id, warehouse_id, inventory_date))" but it shows 450 because I do not know how to pick only the latest date.

Thanks for your help!

Re: total stock kpi

chrismarlow — Mon, 09 Dec 2019 20:31:07 GMT

Hi,
something like;
sum(aggr(FirstSortedValue(stock,-date),[material id],[warehouse id]))
Cheers, Chris.

Re: total stock kpi

Oliver1 — Mon, 09 Dec 2019 20:40:58 GMT

Hi Chris,

thank you so much, it works!

topic total stock kpi in App Development

total stock kpi

Re: total stock kpi

Re: total stock kpi