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: 
peterderrington
Creator II
Creator II

Need an Expression to only show the first value

Hi, 

I'm trying to create a line chart that will show the first value for each date.

 

My data has several lines of data for each date ([Date of Visit]), i want to create a Line chart that just selects the very first entry ([Time called]) for that date.

Does anybody have any idea?

 

Thanks.

12 Replies
tincholiver
Creator III
Creator III

Put your date as dimension, in expression Min(value)
Nicole-Smith

You'll want to use the FirstSortedValue() aggregation function in your expression.

 

FirstSortedValue - chart function

FirstSortedValue() returns the value from the expression specified in value that corresponds to the result of sorting the sort_weight argument, taking into account rank, if specified. If more than one resulting value shares the same sort_weight for the specified rank, the function returns NULL.

Syntax:  

FirstSortedValue([{SetExpression}] [DISTINCT] [TOTAL [<fld {,fld}>]] value, sort_weight [,rank])

 

Full QV documentation here: https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/ChartFunctions/BasicAggregationF...

peterderrington
Creator II
Creator II
Author

No, Tincholiver,  that doesn't show anything

peterderrington
Creator II
Creator II
Author

I can't get the syntax right for your suggestion Nicole, can you clarify?

Nicole-Smith

Dimension:
[Date of Visit]

Expression:
FirstSortedValue([Time called], [Time called])

Note: You may need to change the second argument to something other than [Time called] if it isn't able to use that field for sorting (I can't say whether it will work or not since you haven't shared what your data looks like).

peterderrington
Creator II
Creator II
Author

Hi Nicole,

thanks for that, unfortunately it still isn't displaying anything.

If I set the chart as a straight table I can see that its showing the list of every single date but in the column 'FirstSortedValue' I just get a '-'.

If I set it as the line chart I need it just tells me 'No data to display'.

The data itself is very simple (and attached now)

Three column Headers (there are more but we're only interested in these ones):

'Date of Visit', 'Called For' and 'Returned'

The date of visit column is simply formatted 01/12/2018

And the called for and returned columns are just time codes (08:56, etc).

Ultimately I'm going to need to be able to display the final 'returned' time for a date too.

peterderrington
Creator II
Creator II
Author

Is anyone able to shed any light on what i should be doing, i can't help but feel that we're close to a solution here.

 

Thanks, 

Peter

tincholiver
Creator III
Creator III

Hi Peter, I attach qvw with the solution.

Let me know if it´s good.

Date visit.png

peterderrington
Creator II
Creator II
Author

That works brilliantly, thank you.

I just need to copy the requisite code over to my dashboard.

What I could do with ideally is to then show the final 'return' of that date (the chart is a line chart), I don't suppose you have any clever tricks to do that?

 

Its difficult because the 'largest' return time could be anywhere within that data.