Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andriesb
Creator II
Creator II

Using FirstSortedValue does not always work filtering on latest Date

I have a list of values (prices) where I would like to show in a chart the last value based on the latest date. Using the value fistsortedValue works only sometimes. Why is this ?

In my example :

Pricing could be set on a combination of (project + item) or (project + item + costtype) or on (project + costtype). There could be for any combination more than one price.Sometimes the costtype value is empty

Source example:

ProjectItem1DescriptionCost typecosttype descriptionlatest pricelatest date
Project1I1Item 112.5001-01-2016
Project1I1Item 1cost typecost description01-01-2017

Outcome would be :

First 4 columns are dimensions, the last 5 are outcome of expressions.

Extrating the [latest price]  i've used =FirstSortedValue( (Price), - [Price.Date]   ) .

Also i've tried to use Max( {< Price.Date={$(=Max(Price.Date))} >} Price )

It seems that as soon as an item does have a cost type my firstsportedvalue will fail and he column

[laatste prijs] is empty.


How could I retrieve my latest price using the max(.price.date) based on the shown dimention? Max(price) would not work as there could have been a price drop in the history.

3 Replies
Gysbert_Wassenaar

Can you post a small qlikview document that demonstrates the problem? It's impossible to tell what's going on without looking at the data model and the data. See this document for how to create such an example safely: Preparing examples for Upload - Reduction and Data Scrambling


talk is cheap, supply exceeds demand
andriesb
Creator II
Creator II
Author

It seems that the left join, where I added the desctiption of dthe cost types to my initial tabel , ruines the combined table for convertion text to numbers.  I've added the description by using applymap and forces text() and  now the Firstsortedvalue does work.

My moment of learning: if in doubt, strip all unnessasary scripting and test on 'naked' initial data.

Gysbert_Wassenaar

The join probably caused duplicate records. Then for the last date there would have been multiple price values and the FirstSortedValue couldn't return a result.

That a mapping table did work suggests that your code-description table may have had duplicate records or several records with the same cost type code, but different description values. You might want to check that out to make sure you're not unintentionally sweeping a data quality problem under the rug.


talk is cheap, supply exceeds demand