topic Re: expression for second sorted value in QlikView

expression for second sorted value

peterderrington — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I've been able to create a chart which shows me the utilisation of a location by using FirstSortedValue. My problem is i now need to be able to display the second (and third and forth and so on) value.

The original data is an excel sheet with each row an individual of which we identify the location and also the time of entry to that location as well as the length of time in that location.

The idea is to ultimately create a chart which will show when the location is in use by placing all the occasions of use on one line per location.

This is what i have at the moment:

The Expression i have used is: FirstSortedValue(DISTINCT [Length of Time in Theatres],1,1) and then FirstSortedValue(DISTINCT Into_theatre_Time,1,1) as the bar offset definition.

Any ideas how to go about adding the other instances of area usage?

(for completeness the chart is a stacked bar chart rotated on its side).

Thanks.

Re: expression for second sorted value

marcus_sommer — Fri, 18 Oct 2019 15:06:57 GMT

With the third parameter you could define which n-th value should be returned:

FirstSortedValue(DISTINCT [Length of Time in Theatres],1,n)

- Marcus

Re: expression for second sorted value

peterderrington — Fri, 18 Oct 2019 15:08:54 GMT

To see if i could work out some of the expressions i've been playing about with a straight table and i can manage to display the time of the second usage (and third etc) with this:

FirstSortedValue(Into_theatre_Time,Into_theatre_Time,2)

However i can't manage to fit that into the bar chart.

I used the expression of

FirstSortedValue([Length of Time in Theatres],[Length of Time in Theatres],2)

and then used the Bar Offset of

FirstSortedValue(Into_theatre_Time,Into_theatre_Time,2)

but it put the usage miles away from where it should be.

Re: expression for second sorted value

peterderrington — Fri, 18 Oct 2019 15:38:58 GMT

Ok,

Clearly very very close now.

Using the bar offset of

FirstSortedValue(Into_theatre_Time,1,2)

and then changing that last 2 to a 3 and so on allows the bars to show but the gap between the sections isnt correct.

It look like this:

Now there should be some gaps between those sections (as the location can't be used continually) and certainly the Into_theatre_Time for the second (and subsequent) occasion is after the end of the previous [Length of Time in Theatres] time code.

Any ideas?

Re: expression for second sorted value

peterderrington — Fri, 18 Oct 2019 15:57:18 GMT

The more i dig the worse it gets.

i've filtered down to just one location on one date to make it easier to troubleshoot.

There are 4 people in the location that day.

P1 - 08:31 in - 10:49 out - Length of time: 2h 18m

P2 - 10:56 in - 13:20 out - Length of time: 2h 24m

P3 - 13:53 in - 15:35 out - Length of time: 1h 42m

P4 - 15:45 in - 16:39 out - Length of time: 54m

However if i hover over the graph it shows for length of time:

P1 - 2h 18m

P2 - 1h 42m

P3 - 2h 18m

P4 - 2h 24m

So not only is it not returning the correct Length of time its then also not displaying the offset correctly.

Re: expression for second sorted value

marcus_sommer — Mon, 21 Oct 2019 10:00:54 GMT

If it's worked within a table but not within a chart it might be caused from the applied dimensions - means that the table is more granular as the chart. In this case you may need to apply an aggr-wrapping for your expression in the chart.

Further your approach looked a bit like a gantt chart and therefore the following might be useful in any way:

Recipe-for-a-Gantt-chart
Files-for-creation-of-a-Gantt-chart

- Marcus

Re: expression for second sorted value

peterderrington — Mon, 21 Oct 2019 10:28:49 GMT

It hasn't though. That's the problem.

Trying to identify the correct order is the first problem, then I can work out how to do the offset.

I will have a look at the Gantt chart stuff but I think I've looked at that before and I wasn't able to recreate it, this is the closest I've got before.