Qlik Community

Community Corner

Discussion board for questions about the Qlik Community, its features, sharing information, general discussions and even some fun. This is for both new and longtime community members. Everyone is welcome!

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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
Highlighted
MVP & Luminary
MVP & Luminary

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
Highlighted
Creator
Creator

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.

Highlighted
MVP & Luminary
MVP & Luminary

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