Date of last movement in Query

[Former Member]

Dear all,

I have a query in which I would like to display the last movement of goods. The Key figures are

1. Quantity receipt stock

2. Quantity issued stock

I would like to get the last date of either last receipt stock amount or the last issued stock amount.

I tried to do so by implementing two conditions

Key figure = Quantity receipt stock - TOP N - Value = 1 -- Characteristic Assignment = Individual Chars and chars combination

Unfortunately this is not working. It delivers the biggest movement but not the last.

Could you please help me.

Thanks

Stefan

[Former Member · Accepted solution]

Never done this but just off the top of my head, you could create a formula variable (replacement path) to take the value of calendar day and then apply the top N condition to that variable.

Since it looks like you're dealing with non-cumulatives, that would give the latest dates = latest movements.

Again, this has to be tested. Just an idea.

Hope it helps.

[Former Member]

Hello,

thank you very much with your help. This was a good hint although it takes a bit more to get to a solution.

Nevertheless it works now.

Procedure:

As I mentioned in my first post, I have two key figures.

So as a first step I created a formula in which I checked the following parameters

(( Key Figure1 <> 0) OR (Key Figure2 <> 0)) * 0Calday

So every value is 0 except of the dates there was a movement within one of the two Key Figures.

After that I made a TOP N Condition on that formula with value 1.

I renamed the Calender Day within the query and voilá I had the last movement day.

Best regards

Stefan