Set analysis - Get only max transaction date from each location

[Florentin74]

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:

Location	Item	Date	QTY transaction	QTY Balance
1	1	1th of dec 2016	1	2
1	1	4th of dec 2016	4	6
1	1	10th of dec 2016	-2	4
2	2	4th of apr 2017	10	14
2	2	8th of may 2017	-5	9

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.

[luismadriz · Accepted solution]

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

[Florentin74]

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

[luismadriz]

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