Announcements
cancel
Showing results for
Did you mean:
Partner - Creator

## Set analysis - Get only max transaction date from each location

In an inventory app, i have the inventory balances per item per location but only per day where there was an actual inventory transaction on an item...

Meaning:

LocationItemDateQTY transactionQTY Balance
111th of dec 201612
114th of dec 201646
1110th of dec 2016-24
224th of apr 20171014
228th of may 2017-59

Ok... If I want to know what the inventory balance of item 1 and 2 was on for example the 15th of december 2017. Notice, that is the newest QTY balance per item per location I want... Meaning that Item 2 on location 2 has the balance of 9 on the 15th of december, since that is the latest balance I have... I might also want to know what the balance was on the 6th of may 2017...... The result :  14...

So... Can this be done only with set analysis?

I was thinking something like:  Date <= "selected date", but should be the max date per location per item...

How to?

Regards Anders.

1 Solution

Accepted Solutions
Specialist

Hi,

Interesting!

Something like this?

This is the one for the [Qty Balance]

=AGGR(FirstSortedValue({<Date={"<=\$(=GetFieldSelections(DateSelection))"}>} [QTY Balance],-Date),Location,Item)

Maybe there are better ways of doing it,

I hope this helps,

Cheers,

Luis

3 Replies
Specialist

Hi,

Interesting!

Something like this?

This is the one for the [Qty Balance]

=AGGR(FirstSortedValue({<Date={"<=\$(=GetFieldSelections(DateSelection))"}>} [QTY Balance],-Date),Location,Item)

Maybe there are better ways of doing it,

I hope this helps,

Cheers,

Luis

Partner - Creator
Author

Thanks... I got it working... But I needed a SUM around the AGGR, where I selected the date <= max(date)... in set analysis... so it ended up looking something like this:

SUM(({<InventoryDate={"<=\$(=Max(Date))"}>}AGGR(FirstSortedValue({<InventoryDate={"<=\$(=Max(Date))"}>} [QTY Balance],-InventoryDate),Location,Item))

Thanks a lot...

Wonder if a Rank funktion could have been used also....

Regards,

Anders

Specialist

Hi,

I think Rank would have given you the position according to something, then you would've needed to get the value (QTY Balance) based on that position. FirstSortedValue gets you the value based on that position.

But I'm sure there are other ways of doing the same. I think it's good to find other ways of doing especially if there are performance issues. With small databases you could get away with almost anything...

Cheers,

Luis

Community Browser