topic Sum Distinct Stocks of Product per Date in App Development

Sum Distinct Stocks of Product per Date

brunolovatti — Thu, 13 Jan 2022 20:15:54 GMT

Hi!

In the sample table

Date	Product	Order	Stock
12/01/2022	A	-	100
12/01/2022	C	O1	50
13/01/2022	A	O2	120
13/01/2022	A	O3	120
13/01/2022	B	O4	20
13/01/2022	C	-	0

I need to SUM the last stock per each product dimension.

Date	Product	Stock
13/01/2022	A	120
13/01/2022	B	20
13/01/2022	C	0

The SUM of the last stock is 140 and if there is more than one record per day per product the stock value is always the same, product A in the example.

IF we had only one record per date + product, the measure would be:

Sum(
{
1
<
[Date]={"$(=Max({1}Date))"}
>
}
[Stock]
)

But our data repeats the stock for the number of orders of each product per day.

I tried to use FirstSortedValue and aggr distinct with set analysis but could not get it to work yet.

Thank you!

Bruno

Re: Sum Distinct Stocks of Product per Date

brunobertels — Fri, 14 Jan 2022 08:16:38 GMT

May be this

Sum(
{
1
<
[Date]={"$(=Max({1}Date))"},Order={"$(=max({1}Order))"}
>
}
[Stock]
)

Re: Sum Distinct Stocks of Product per Date

brunolovatti — Fri, 14 Jan 2022 11:51:40 GMT

Did not work. It returns 20. Should be 140.

Thank you!

Sample data is:

orders:
Load
Date(date) as data, order, product, stock
Inline
[date, order, product, stock
'13/01/2021', null, 'A', 100
'13/01/2021', 1, 'C', 50

'14/01/2021', 2, 'A', 120
'14/01/2021', 3, 'A', 120
'14/01/2021', 4, 'B', 20
'14/01/2021', null, 'C', 0
];

Re: Sum Distinct Stocks of Product per Date

brunolovatti — Fri, 14 Jan 2022 12:31:37 GMT

This worked for this sample:

Sum(
{
1
<
[date]={"$(=Max({1}date))"}
>
}
distinct stock
)

And for the original project i think this is correct too:

Sum(
{
1
<
[Date]={"$(=Max({1}Date))"}
>
}
aggr(
Sum(
{
1
<
[Date]={"$(=Max({1}Date))"}
>
}
distinct [stock]
)
,DateStoreProduct
)
)

With a combined key (date + store + product).