topic Re: Evaluate expression for latest date in QlikView

Evaluate expression for latest date

Fri, 24 Oct 2014 17:48:52 GMT

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

Re: Evaluate expression for latest date

JonnyPoole — Fri, 24 Oct 2014 18:13:16 GMT

Try:

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

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

Re: Evaluate expression for latest date

maxgro — Fri, 24 Oct 2014 18:50:29 GMT

just add a date in your exp (bold)

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

Re: Evaluate expression for latest date

Fri, 24 Oct 2014 19:11:20 GMT

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.

Re: Evaluate expression for latest date

JonnyPoole — Fri, 24 Oct 2014 19:29:12 GMT

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))

Re: Evaluate expression for latest date

Fri, 24 Oct 2014 20:03:16 GMT

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?

Re: Evaluate expression for latest date

JonnyPoole — Fri, 24 Oct 2014 20:10:13 GMT

I would try just an IF statement within the MAX.

Change:

Max(Date)

To:

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

Re: Evaluate expression for latest date

Fri, 24 Oct 2014 20:24:49 GMT

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