Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dimension
PartNumber
Expression
FirstSortedValue(Price,-InvoiceDate)
1) Giving me '-' whenever same part number is sold more than once on the same date.
2) What if same part number is sold more than once with different price
My Idea is to get Minimum Price for each part number sold on the latest Date...
How about:
=FirstSortedValue(DISTINCT Aggr(Min(Price),PartNumber,InvoiceDate),-InvoiceDate)
-Rob
Try
FirstSortedValue(Aggr(Min(Price),PartNumber,InvoiceDate),-InvoiceDate)
HIC
Hi Henric,
Its not working...
Please check enclosed file..
You can do it using a cross-table and the Above() function.
LastPrice = If(IsNull(Min(Price)),Above(LastPrice),Min(Price))
Dear Henric,
Thanks for your valuable time but this will not work for me...
I have around 80,000 part numbers...
The preferable solution should be as per below table.
PartNumber | MinPrice | MaxInvoiceDate |
A | 105 | 08/01/2014 |
B | 110 | 04/01/2014 |
C | 130 | 10/01/2014 |
D | 110 | 07/01/2014 |
Any reply on this?
Your help would be highly appreciated...
This might not be the most elegant, but you could sort using the concat function. Try this out:
subfield(concat(aggr(min(Price), PartNumber, InvoiceDate), '|', -InvoiceDate), '|', 1)
Hope this helps!
Tricky and working but still would love to get another way.
Hi.
This will give you the result:
=FirstSortedValue(Aggr(Min(Price),PartNumber,InvoiceDate),-aggr(InvoiceDate, PartNumber, InvoiceDate))
Hi,
Here is my suggestion:
=Min(if(InvoiceDate=aggr(NODISTINCT Max(InvoiceDate), PartNumber), Price))