expression for second sorted value

[peterderrington]

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.

[marcus_sommer]

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

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

- Marcus

[peterderrington]

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.

[peterderrington]

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?

[peterderrington]

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.

[marcus_sommer]

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

[peterderrington]

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.