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?
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...
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...
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.
