Qlik Community

New to Qlik Community

Discussion board for questions on how to use Qlik Community and its features.

andriesb
Contributor

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

Re: Using FirstSortedValue does not always work filtering on latest Date

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
Contributor

Re: Using FirstSortedValue does not always work filtering on latest Date

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.

Re: Using FirstSortedValue does not always work filtering on latest Date

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
Community Browser