Find laste date a product was in stock

[#1]

Hello,

I'm trying to extract a date when the product last was in stock.

E.g.

Article          Date               Qty

X                  20120215        10

X                  20120201        1

X                  20120115        0

X                  20120101        1

In this example the date for product X is 20120201

Article          Date               Qty

Y                  20120215        0

Y                  20120201        1

Y                  20120115        5

Y                  20120101        10

In this example the date for Y is null because it is not in stock at the moment.

How can I achieve this in the script?

I want to get the earliest date per product where Qty has not been 0 and Qty is not 0 at tme moment.

br

Martin

[CELAMBARASAN · Accepted solution]

Hi,

     Try with this expression

     =If(FirstSortedValue(Qty,-Date),FirstSortedValue(Date,-Date),'Null')

Hope it helps

Celambarasan

[#3]

Hello,

It works, thank you.

I do not quite understand how it works.

You you try and explain how the condition in the if works?

f(FirstSortedValue(Qty,-Date),

If Qty for the earliest Date is what???

br

MArtin

[CELAMBARASAN]

Hi,

     You know about the following

     Condition returns 0 means false.Ex: =if(0,0,1) it returns 1

     Condition returns otherthan 0 means true.

     In your case you need to display null if the qty is 0 FirstSortedDate(Qty,-Date) returns the qty on the latest date because i used ( - ) before the date.

Celambarasan