Need an Expression to only show the first value

[peterderrington]

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.

[tincholiver]

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]

No, Tincholiver,  that doesn't show anything

[peterderrington]

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]

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]

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]

Hi Peter, I attach qvw with the solution.

Let me know if it´s good.

[peterderrington]

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.