Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
AWS Degraded - You may experience Community slowness, timeouts, or trouble accessing: LATEST HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

FirstSortedValue

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!

2 Replies
Not applicable
Author

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

Clever_Anjos
Employee
Employee

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