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...
1th of dec 2016
4th of dec 2016
10th of dec 2016
4th of apr 2017
8th of may 2017
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...
Re: Set analysis - Get only max transaction date from each location
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...