Plotting a change in an average in a Run Chart

[peterderrington]

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.

[peterderrington]

So the values in the data sheet on the example excel file are purely the admission totals per month. If you look at the 'Changing Median Line' sheet in the dashboard then you'll see that the numbers correspond with the totals there.

[Kushal_Chawda]

Which Date I need to pick up and which field I need to count to get the value?

[peterderrington]

So if you look at the expression in the QlikView dashboard (I’m not in front of my machine) at the moment) then you’ll see the expression used to calculate the count (& average)

[Kushal_Chawda]

I don't have access to Qlik at this moment. I can convert it to QVF but then I won't get expressions as objects won't be retain

[peterderrington]

No problem. Give me a little bit of time and I’ll check the expressions and post them back up. Just sorting children and bedtimes 😃

[peterderrington]

So here is what that sheet on the dashboard looks like:

The expression for counting the top graph is:

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

With [Month and Year] being the dimension.

The median line is calculated with this:

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

I drew the bottom graphs with the following expressions and then just placing the two graphs next to each other:

Left Expression:

Count({<AdmitSource={'A&E'},[Month and Year]={'Jul 18','Aug 18','Sep 18','Oct 18','Nov 18','Dec 18','Jan 19','Feb 19','Mar 19','Apr 19','May 19','Jun 19'}>}AdmitSource)

and Left Median line:

Median(TOTAL AGGR (Count({<AdmitSource={'A&E'},[Month and Year]={'Jul 18','Aug 18','Sep 18','Oct 18','Nov 18','Dec 18','Jan 19','Feb 19','Mar 19','Apr 19','May 19','Jun 19'}>}AdmitSource),[Month and Year]))

With the reverse being in the right expression:

Count({<AdmitSource={'A&E'},[Month and Year]={'Jul 19','Aug 19','Sep 19','Oct 19','Nov 19','Dec 19'}>}AdmitSource)

and Right Median line:

Median(TOTAL AGGR (Count({<AdmitSource={'A&E'},[Month and Year]={'Jul 19','Aug 19','Sep 19','Oct 19','Nov 19','Dec 19'}>}AdmitSource),[Month and Year]))

[johnmackintosh]

A really quick, but horrible way to do it:

if( RowNo() <=12, OldValue, New Value)

which in your case:

if( RowNo() <=12, 864.5, 1215)

You can extend this in future if need be, but has the drawback of being hardcoded and therefore does not adapt to any further selections or future requirements.

[Kushal_Chawda]

Do you manually assign median value or it has to be calculated? If it has to calculated then, Jul18-Jul19 median value is not 864 it is 880.

[johnmackintosh]

Median is being calculated from Jul 18  to Jun 19 (not July 19), so 864.5 is correct in this instance.

Personally I would always calculate a run chart median over an odd number of months,  however in this case a change has been made in Jul 19 so its not appropriate to include it in the original median calculations

[Kushal_Chawda]

You need to create a Sequence of [Month & Year] as per the sort criteria

Data:
LOAD Module,
	type,
	EpisodeNumber,
	SpellNumber,
	AttendDateTime,
	LeftDateTime,
	FirstAtt,
	AdmissionMethod,
	AdmitSource,
	lcons,
	LOS4hrs,
	Outcome,
	DischargeDateTime,
	PrimaryDiagnosis,
	PrimaryDiagA3,
	PrimaryDiagDescA3,
	PrimaryProcedure,
	PrimaryProcA3,
	PrimaryProcDescA3,
	Readmitted,
     [Day of the Week], 
     Month, 
     [Week Number], 
     Year, 
     [Month and Year],
     [Length of Stay],
     [Valid Length of Stay]
     
FROM [lib://Data/Files/Ambo Admissions.xlsx]
(ooxml, embedded labels, table is [SQL data2]);

Sequence:
Load distinct [Month and Year],
     ceil(autonumber([Month and Year])/12) as Seq
Resident Data;

Noe create a line chart with Dimension [Month & Year] and below expressions

1) Count({<AdmitSource={'A&E'}>}AdmitSource) --> Count
2) alt(aggr(median(aggr(Count({<AdmitSource={'A&E'}>}AdmitSource),[Month and Year])),Seq),Above(Column(2))) --> Median