FirstSortedValue on Max date

[Hauglaen]

Hi

My dataset consist of a weekly report showing sales and inventory.

And I am having trouble displaying inventory for only the last week.

My current solution displays the latest inventory update for each ID and not only for the last week:

ALT(FirstSortedValue(Inventory,-Date),0)

Any suggestions? 

Thanks!

[agigliotti]

could you explain better what are you trying to achieve?
what is your current result and what is your desired result?

[Hauglaen]

My dataset looks like this:

ID	Inventory	Date
1	20	26.04.2020
2	10	26.04.2020
3	15	26.04.2020
1	10	03.05.2020
2	15	03.05.2020

And I only want to see the latest inventory update:

ID	Inventory	Date
1	10	03.05.2020
2	15	03.05.2020

My current solution includes 15 from ID 3 because its the latest value for the ID.

[agigliotti]

you case use the below expression:

sum( {< Date = {"$(=date(max(Date)))"} >}Inventory)

with Date and ID as dimensions.

[Hauglaen · Accepted solution]

Your expression didnt work, but it sent me on the right path.

The following expression worked for me:

SUM( IF( Date = MAX(TOTAL Date), Inventory, 0 ))

Thank you!

[agigliotti]

please close this thread and mark the reply as solution if applicable.

[Hauglaen]

I tried sum( {< Date = {"$(=date(max( TOTAL Date)))"} >}Inventory)

But it just resulted in zeroes for each ID.