Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I need a syntax for the following problem
I have a table with dimensions; Vendor,Item,Time (po date) and the expression is unit price. What I need is the lastest Po date of that item with the corresponding unit price. With that price I will calculate with other fields. So when I troggle on Vendor or Item and time, I always need the latest PO with unit price from the database.
VENDOR NAME | ITEM | PO LINE CREATION DATE | UNIT PRICE |
AxFloX | 005870 | 14-1-2014 | 37,62 |
AxFloX | 005870 | 17-6-2014 | 40,68 |
I think this can be achieved with the correct set analysis in the presentation layer but perhaps a solution in the script is also no problem...
Thanks in advance
Best regards
Robert
Try first sorted value
Like below
FirstSortedValue(UNIT PRICE, -PO LINE CREATION DATE)
Regards
ASHFAQ
I should solve on script side, simply doing this:
If your table name is myTable then
Load * resident myTable;
left join
Load max([creation date]) as [creation date], '1' as flag resident myTable group by [Vendor Name], Item;
Now you have a flag field showing max (per Vendor and Item), use this field in your expression with set analysis and it will work
Let me Know