topic Re: Plotting a change in an average in a Run Chart in QlikView

Plotting a change in an average in a Run Chart

peterderrington — Mon, 13 Jan 2020 10:56:33 GMT

Hi,

I have a Run Chart that shows activity over a period of months, i have been able to plot a Median Average on the graph however i need to be able to show a change in activity on the graph which would then alter the Median average 'mid run'.

This is the current graph:

This is the type of thing i want to make (which is easy to do in Excel):

Does anybody have any idea how to achieve this in QlikView? I can't believe i can do it in Excel but not QlikView, i'm clearly missing something obvious.

Thanks.

Re: Plotting a change in an average in a Run Chart

Kushal_Chawda — Mon, 13 Jan 2020 12:02:13 GMT

How would you alter the median? Would you be able to share the sample data?

Re: Plotting a change in an average in a Run Chart

peterderrington — Mon, 13 Jan 2020 13:47:03 GMT

So the median would change from a specific date. The idea is to show what improvement (or decline) has occurred since the implementation of a new service.

The Excel data looks similar to this:

With Jan 19 being the start of the new phase.

Re: Plotting a change in an average in a Run Chart

qliksus — Tue, 14 Jan 2020 10:17:52 GMT

Something like the below

Median( Total<Month> aggr( sum(Value) , Month))

Re: Plotting a change in an average in a Run Chart

peterderrington — Tue, 14 Jan 2020 10:29:58 GMT

ok....

I think I might need a little bit more advice.

I have [Month and Year] as the dimension (which displays like Jul 18, Aug 18, etc) and then the current expression is showing as:

Count({<AdmitSource={'A&E'}>}AdmitSource)

as I want to pick out the specific A&E visits first (there are three types and I will repeat with all three) with the Median average being calculated by this expression:

Median(TOTAL AGGR (Count({<AdmitSource={'A&E'}>}AdmitSource),[Month and Year]))

How would I alter your example to replicate what i'm after?

Re: Plotting a change in an average in a Run Chart

Kushal_Chawda — Tue, 14 Jan 2020 12:11:46 GMT

Hi, If you already have median available in data, how about just creating expression sum(median) in chart?

Re: Plotting a change in an average in a Run Chart

peterderrington — Tue, 14 Jan 2020 12:20:50 GMT

But how would that recalculate the median line at the point of change?

Re: Plotting a change in an average in a Run Chart

Kushal_Chawda — Tue, 14 Jan 2020 13:32:26 GMT

try this expression

Median(TOTAL <Month> AGGR (Count({<AdmitSource={'A&E'}>}AdmitSource),[Month and Year]))

Re: Plotting a change in an average in a Run Chart

peterderrington — Tue, 14 Jan 2020 16:16:31 GMT

If I put that in as typed then I just get the exact same median line as the one I already have. Where would I put in the identifier of the point of change (in this instance July 19).

Is it possible, once we've worked out how to do this to be able to add an additional change point?

Re: Plotting a change in an average in a Run Chart

johnmackintosh — Tue, 14 Jan 2020 16:23:39 GMT

Hi Peter

I'm assuming that you want to change the median when a signal occurs.

Typically, that signal is a run of 9 consecutive points on the desired side of the median line.

This run chart analysis is actually pretty tricky, so you're probably going to want to only have one measure per chart .

( 3 measures, with 3 unique medians, is going to be really confusing visually)

Do you have a flag column to compare if the current value is higher / lower than the current median?

John

Re: Plotting a change in an average in a Run Chart

Kushal_Chawda — Tue, 14 Jan 2020 16:30:39 GMT

Would you be able to share the sample data with expected output?

Re: Plotting a change in an average in a Run Chart

peterderrington — Tue, 14 Jan 2020 17:15:32 GMT

So what I'm looking to do is essentially reset the median line at a specific, given point in time. (in this particular instance - July 19 as that was when we introduced a change idea)

I want it to show the median line increase (or decrease dependant on outcome) as in this image (from Excel):

So that we have the next two images but as one image showing the whole timeline:

I can add the excel data if that would be helpful.

Re: Plotting a change in an average in a Run Chart

peterderrington — Tue, 14 Jan 2020 17:17:06 GMT

Obviously the image from Excel has the change point at the wrong point (Jan instead of Jul) but it gives you a good indication of how it should look.

Re: Plotting a change in an average in a Run Chart

peterderrington — Mon, 20 Jan 2020 15:11:43 GMT

Did anybody have any ideas as to how to produce the graph?

Re: Plotting a change in an average in a Run Chart

Kushal_Chawda — Mon, 20 Jan 2020 15:27:47 GMT

If you can share sample data with file it will be easier to get the answers.

Re: Plotting a change in an average in a Run Chart

peterderrington — Mon, 20 Jan 2020 16:01:10 GMT

Hi,

Please see attached an example of the data and the dashboard (in the next section as I don't seem to be able to attach two items).

The Sheet entitled 'Changing Median Line' shows both the current expressions and a mock up of what i'm hoping to create (by placing two graphs side by side - heavily filtered).

Many thanks,
Peter

Re: Plotting a change in an average in a Run Chart

peterderrington — Mon, 20 Jan 2020 16:02:09 GMT

and the dashboard.....

Re: Plotting a change in an average in a Run Chart

Kushal_Chawda — Mon, 20 Jan 2020 16:10:12 GMT

In excel file I can see only one sheet of data

Re: Plotting a change in an average in a Run Chart

peterderrington — Mon, 20 Jan 2020 16:27:28 GMT

Yes the Excel file only has one sheet.

The Changing Median Line sheet is on the dashboard.

Attached is the original template I used to generate the excel image.

Re: Plotting a change in an average in a Run Chart

Kushal_Chawda — Mon, 20 Jan 2020 16:42:31 GMT

How you derive Values in Example sheet from Data sheet?