FirstSortedValue

Mon, 20 Jan 2014 16:40:28 GMT

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!

Re: FirstSortedValue

Mon, 20 Jan 2014 16:46:49 GMT

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

Re: FirstSortedValue

Clever_Anjos — Tue, 21 Jan 2014 00:38:47 GMT

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

topic Re: FirstSortedValue in QlikView

FirstSortedValue

Re: FirstSortedValue

Re: FirstSortedValue