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?
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.
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...
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.
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))
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.