Problem with first selected value

[peterderrington]

So I have a chart that shows the first instance of someone entering (and leaving) a department which uses these expressions:

Time(FirstSortedValue([Time Event Sent For],ID,1))

Time(FirstSortedValue([Time Event Out Of Recovery],-ID,1))

However the chart only works if I don't filter down to a specific department which isn't helpful.

Can anyone help?

[sergio0592]

Hi,

Try with

Time(FirstSortedValue({<Department>}  [Time Event Sent For],ID,1))

Time(FirstSortedValue({<Department>}  [Time Event Out Of Recovery],-ID,1))

[peterderrington]

So we're a step forward....

It now doesn't show the dreaded 'No Data' message now when I select a department however the graph itself doesn't change. So I think what its doing is just showing the very first selected value of all the data rather than the selected.

[jonathandienst]

That set expression overrides the selection of Department. Its hard to give specific advice without knowing more of your data model, but perhaps you need a partial total, something like this:

Time(FirstSortedValue(TOTAL <Department> [Time Event Sent For], ID ,1))

Time(FirstSortedValue(TOTAL <Department>} [Time Event Out Of Recovery], -ID, 1))

You may need to try other field(s) in the partial total. This is only a guess however...

[peterderrington]

So that's not working properly.

The graph goes flat and disappears completely when you select a department.

Attached is what the graph should look like.

[sunny_talwar]

Does this happen for every selection of Department? Also, do you have multiple lowest value per Department, if you do, you can try adding a DISTINCT like this

Time(FirstSortedValue(DISTINCT [Time Event Sent For], ID, 1))
Time(FirstSortedValue(DISTINCT [Time Event Out Of Recovery], -ID, 1))

[peterderrington]

Yeah it happens for all departments selected.

The original data is an excel spreadsheet, in time order, with various lines for different instances. Each line contains a number of unique identifiers and also has a time that they started (or were called for) and when they finished.

I need to be able to select a department and then see the very first instance that someone was called into the department and then the last time that someone left that department too.

[peterderrington]

Can anyone else help?

[sunny_talwar]

In order to help you better, would you be able to share a qvw sample where we can see the problem?

[peterderrington]

I'll see if i can sort out a qvw sample and post it up, i just need to anonomise some of the data first.

Thanks.