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]

Hi, 

Sorry for the delay in getting back to you (sickness and workload!)

I'm trying the suggestion you made Kush but whenever i reload the script i get the error message:

Table not found error

Table 'Data' not found

Sequence:

Load distinct [Month and Year], ceil(autonumber([Month and Year])/12) as Seq

Resident Data

Any ideas?

[Kushal_Chawda]

When you take a resident please check that previous table name is 'Data' ,if it is different define that name in resident 

[peterderrington]

ok...… I'm not entirely sure what you mean by that. (sorry)

Here is the current script:

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
[R:\Transformation Team\Projects\Open\Abdo Pain\Data\Ambo Admissions.xlsx]
(ooxml, embedded labels);

LOAD [ED - Attendance Number],
[ED - Arrival Date],
[ED - Arrival Time],
[ED - Departure Date],
[ED - Departure Time],
[ED - Arrival Department Wait (mins)],
[ED - Total Time in Dept],
[ED Arrival Timestamp],
[ED Departure Timestamp],
[Day of the Week],
Month,
[Week Number],
Year,
[Month and Year]
FROM
[R:\Transformation Team\Projects\Open\Abdo Pain\Data\ED to Ambo Patients.xlsx]
(ooxml, embedded labels, table is Sheet1);

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 [R:\Transformation Team\Projects\Open\Abdo Pain\Data\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;

[Kushal_Chawda · Accepted solution]

I think it should be like below. Data table load is your actual source table load

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
[R:\Transformation Team\Projects\Open\Abdo Pain\Data\Ambo Admissions.xlsx]
(ooxml, embedded labels);

New:

LOAD [ED - Attendance Number],
[ED - Arrival Date],
[ED - Arrival Time],
[ED - Departure Date],
[ED - Departure Time],
[ED - Arrival Department Wait (mins)],
[ED - Total Time in Dept],
[ED Arrival Timestamp],
[ED Departure Timestamp],
[Day of the Week],
Month,
[Week Number],
Year,
[Month and Year]
FROM
[R:\Transformation Team\Projects\Open\Abdo Pain\Data\ED to Ambo Patients.xlsx]
(ooxml, embedded labels, table is Sheet1);

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

[peterderrington]

That is fantastic.

It produces the exact image I was after.

Two small questions....

I don't fully understand how the expression knows that July is the date I need it to recalculate

and would it in theory be possible to have additional median line changes?

Thank you.

[peterderrington]

Is anybody able to come back to me with the answers to my question?

Would be helpful if i could understand how the expression works so i can replicate in other sections.

Thanks.