Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello.
I have load a table with warehouse handling, with positive and negative movements from the first movement to now (ex. ITEM, date, sign, and other fields).
I use a pivot table to sum and get the warehouse actual quantity per item and I need to find last price per item:
DATE (year, month, day), ITEM as dimension, QUANTITY, LAST PRICE, VALUE (column 1 * column 2) as expressions
* selection: today (so that i sum every movements from the first to today and get actual quantity; but the user can change the date "to" to another, like if he want to know the quantity per item until 31/12/2012 for example and in that option the "last price" is to be the last price until 31/12/2012 )
I use FirstSortedValue and it seems to work ok but only in the detail level (year\month\day\item ok; if I group to day or month or year sometimes the "last price" is blank and so the "value" column).
(is a bit more complex because I need to switch from 2 different cost with a variable set by the user and not consider day/month/year selections )
FirstSortedValue({$<year=, month=, day= , magDate={"<=$(vDataRifUS)"}>}
Aggr({$<year=, month=, day= , magDate={"<=$(=vDataRifUS)"}>}
if( vCostType=1, magCost, magCost2), magItem, magDate)
, -magDate)
I do not know why is computed only in the 4 level (detail with item).
Do you have any idea about the blanks when columns are grouped?
or idea to get the last price dynamically ?
Thanks!
Hi,
FirstSortedValue returns Null when more than 1 value share the same lowest order, certainly why you have blanks when data is grouped. Maybe you need another critera to ensure there's always 1 value; you can also try FirstSortedValue(Distinct ...
When two or more lines 'ties' to aggregation level, FirstSortedValue will return "null".
You should provide a "untie" value enabling FirstSortedValue to choose wich value it should return