topic Display Latest Data in QlikView

Display Latest Data

Mon, 13 Dec 2010 22:10:57 GMT

Good Morning-

I'm hoping the more experienced QV users can help me with a logic issue.

I have a straight table with several dimensions and expressions. I'm trying to show the most recent cost for items no matter when they were ordered. The table below shows the key elements (including the date field, which I would remove from the output) that I'd like to have rolled up to one line.

StockNo	Desc	EndDate	DocCnt	UnitPrice
1021	WIDGIT	2010141	1	$2,166.00
1021	WIDGIT	2010112	1	$2,100.00
1021	WIDGIT	2010004	1	$2,272.00
1021	WIDGIT	2009310	1	$1,999.00

Using this logic, I should see a UnitPrice of $2166.00 with a count of 4. Here's what I'm looking to output:

StockNo	Desc	DocCnt	UnitPrice
1021	WIDGIT	4	$2,166.00

I've tried if(END_DATE=max(END_DATE),UNITPRICE) but that doesn't work. How can I do this without doing an IF condition on the END_DATE field? Any ideas on what I can try?

Display Latest Data

Oleg_Troyansky — Mon, 13 Dec 2010 22:24:15 GMT

Use function "FirstSortedValue", using Date * -1 as your sorting parameter.

Keep in mind that this is an aggregation funtion. If you need to use the result inside another aggregation function, you'll have to use Advanced Aggregation (function AGGR() ).

If you could pre-calculated the most recent Price in the load script, your expressions could be much simpler...

Display Latest Data

Tue, 14 Dec 2010 15:48:05 GMT

FirstSortedValue(distinct NETPRICE, END_DATE * -1)

Works perfectly and exactly what I was looking for. Thanks for the help!

ps - I'm looking for the Verified Answer option but don't see it in the post. Soon as I find it, I'll verify. Thanks again...

Display Latest Data

rwunderlich — Tue, 14 Dec 2010 16:17:00 GMT


JuanitaR wrote:
ps - I'm looking for the Verified Answer option but don't see it in the post. Soon as I find it, I'll verify. Thanks again...

Verify option was missing because your initial post was submitted as a "discussion" instead of a "question". I changed the type to question - verify is available now.

-Rob

Display Latest Data

Tue, 14 Dec 2010 22:08:28 GMT

Got it. I'll be sure to post as a question in the future!