topic Re: Problem with first selected value in QlikView

Problem with first selected value

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

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?

Re: Problem with first selected value

sergio0592 — Fri, 06 Sep 2019 09:41:15 GMT

Hi,

Try with

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

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

Re: Problem with first selected value

peterderrington — Fri, 06 Sep 2019 09:50:56 GMT

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.

Re: Problem with first selected value

jonathandienst — Fri, 06 Sep 2019 10:04:45 GMT

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

Re: Problem with first selected value

peterderrington — Fri, 06 Sep 2019 10:20:35 GMT

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.

Re: Problem with first selected value

sunny_talwar — Fri, 06 Sep 2019 11:00:13 GMT

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

Re: Problem with first selected value

peterderrington — Fri, 06 Sep 2019 14:01:24 GMT

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.

Re: Problem with first selected value

peterderrington — Tue, 17 Sep 2019 11:40:15 GMT

Can anyone else help?

Re: Problem with first selected value

sunny_talwar — Tue, 17 Sep 2019 11:47:55 GMT

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

Re: Problem with first selected value

peterderrington — Tue, 17 Sep 2019 12:06:41 GMT

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.

Re: Problem with first selected value

peterderrington — Tue, 17 Sep 2019 12:19:46 GMT

Ok,

Try this....

Re: Problem with first selected value

sunny_talwar — Tue, 17 Sep 2019 12:27:19 GMT

So, I added Theatre Name dimension to the chart (after converting it to a straight table) and I see all Nulls, seems like Theatre Name is not connected to Date of Visit dimension.

Re: Problem with first selected value

peterderrington — Tue, 17 Sep 2019 12:35:47 GMT

Strange,

Here is a version of the data