Evaluate expression for latest date

[#1]

I'm trying to get a product to evaluate it's unit cost by the last time it was changed in the system. I can't seem to get it working properly, based on other questions that have been posed in the past. I've checked quite a few other discussions, and none seem to solve my problem.

Current Expression:  sum( {< Date = {"=$(=max(Date))"}>} Value)/sum( {<Date = {"=$(=max(Date))"}>} Quantity)

Example Attached.

Thanks in advance,

Alex

[JonnyPoole]

Try:

sum( {< Date = {'$(=Date(max(Date)))'}>} Value)

/

sum( {<Date = {'$(=Date(max(Date)))'}>} Quantity)

[maxgro]

just add a date in your exp (bold)

sum( {< Date = {"$(=date(max(Date)))"}>} Value) /sum( {<Date = {"$(=date(max(Date)))"}>} Quantity)

[#4]

That appears to work in a small application with a few products. I'm now wrestling as to why it's not working with a larger data set.  With no selections at all, the table becomes a one row table with the product name and unit cost being null. However, if I select individual products or multiple products, it works fine. If I select all products, only one product shows and it's unit cost is null. Thoughts on why that isn't working? I've tried popping the data from the product that shows when all are selected into the file I uploaded and it works fine.

[JonnyPoole]

Alex , i'm not 100% sure.

I wonder if you put this expression in a text box in your new model whether it bring back a real date ?

=Date(max(Date))

[#6]

I think I've pinpointed the issue. If the latest date for a product has no corresponding value/quantity, it seems to break the entire table. I am trying to adjust the set analysis to check for the latest date with a non-null value and quantity. Thoughts on that?

My thought would be to do a set analysis inside the max function which is inside the set analysis for a sum function?
Is that viable? How's the computation for that if that would be the solution?

[JonnyPoole · Accepted solution]

I would try just an IF statement within the MAX.

Change:

Max(Date)

To:

Max(   if( not (isnull(Value) or isnull(Quantity)) , Date) )  

[#8]

I feel dirty for using that, but it works. Thanks! Enjoy your weekend.