topic Re: QlikView Experts: Control Chart Challenge in QlikView

QlikView Experts: Control Chart Challenge

danielgargiulo — Tue, 07 Jan 2014 05:55:43 GMT

Control Chart Challenge

If you are a QlikView whizz it would be great to see if you can solve this particularly interesting challenge and how you go about. Good Luck!!!

Control Charts are used extensively in health care and the creation of these including highlighting rules has been nicely outlined by Erica in her blog (http://qlikfit.blogspot.co.uk/).

The interesting challenge we are currently facing is re calculating the average when there are 8 consecutive points above (or below) the average. Once this rule has been meet the average from the 9^th point onwards needs to be re calculated to be based only on the data points going forward. This then needs to be taken further to check again for another 8 points above or below the new average, and so on.

We have spent countless hours trying to solve this and got nowhere!!!!!!!!!!!!

Please find attached:

‘Data Average Changes.xlsx’: Data and an image illustrating the step up in average we need to create
‘Control Chart Template - Jumping Average.qvw’ : Example QlikView application with the data and a control chart that could be used as a starting point.

I look forward to seeing your solutions.

Dan

Re: QlikView Experts: Control Chart Challenge

Wed, 08 Jan 2014 11:31:36 GMT

Hi Daniel

Interesting challenge! I suspect this might be something best tackled in the script, will have a look later on tonight

Erica

Re: QlikView Experts: Control Chart Challenge

Anonymous — Wed, 08 Jan 2014 11:49:31 GMT

Dear,

I also looked into the document and indeed very interesting challenge but i'm a bit confused about the correct/wanted result.

Could you repost the Excel after you added a collumn with the correct/wanted average per line ? So I can see where it changes and how it is calculated.

Most of the time a Excel with the correct data brings 50% of the sollution to these 'challenges'.

Paul

Re: QlikView Experts: Control Chart Challenge

danielgargiulo — Wed, 08 Jan 2014 19:25:24 GMT

Hi Paul,

Thanks for the interest in the challange.

I have attached a new Excel document with some of the working, note in this example i am only checking for points above the line however if we can resolve the issue for points above the line we will be able to also apply it to points below the line. Please also note a lot of this is done manually in my example eg having to identify the 9th point and then restarting the running average in a new column. Hopefully this is clearer as to desired outcomes. Just let me know if there is anything else.

Please note that we need to be comparing each data point to a RUNNING average.

Dan

Re: Re: QlikView Experts: Control Chart Challenge

Anonymous — Thu, 09 Jan 2014 00:10:18 GMT

Hi,

I think the Excel-solution is not far away.

Attached is a Excel with the same results as you gave me but I still have to copy 2 columns and manualy activate the formula.

I don't think it is a good moment to look into the Excel, but I would ask you if you could give me a new list of data (only Date + Data is OK, no chart, no correct figures) and I will use the formules to predict the results. After that it will be up to you to check these and if this is OK I think we can continue to Qlikview.

PS: I don't think it is possible to solve the challlange with formules, but will have to use the script I suppose, and that could slow down the load. Are we talking about great number of records ?

PS2 : Erica if you find the solution first, please do NOT say it was easy, it would ruin my week

Paul

Re: Re: Re: QlikView Experts: Control Chart Challenge

danielgargiulo — Thu, 09 Jan 2014 00:48:07 GMT

Hi Paul,

This is definitely another step closer.

I have attached another spreadsheet with new data. The first tab is the same data as before however with no chart and extra columns. The second sheet i have modified the records in the data column.

The data sets will not be large so load times should not be an issue. My only concern with doing this in the script is that the chart will not be dynamic for example if we wanted to filter for a particular year or region. However if we can do it in the script that is still better than manually in excel.

Thanks again,

Dan

Re: Re: Re: QlikView Experts: Control Chart Challenge

danielgargiulo — Thu, 09 Jan 2014 00:48:56 GMT

Thanks Erica, i look forward to seeing how you go.

Re: Re: Re: Re: QlikView Experts: Control Chart Challenge

flipside — Thu, 09 Jan 2014 09:16:16 GMT

Here's a solution in script, I think, using some random data, but it proves the method.

// Sample Data - row order will need to be accurate to ensure correct calculation later

Data1:
Load
Rowno() & ':' & ID & ':' & Value as UID,
* inline [
ID, Value
1, 85
2, 74
3, 83
4, 62
5, 81
6, 80
7, 79
8, 78
9, 77
10, 86
11, 85
12, 86
13, 88
14, 89
15, 90
16, 91
17, 91
18, 92
19, 86
20, 88
21, 98
22, 97
23, 98
24, 97];

Let SeedSample = 3; //specify how many rows to sample to start off average
Let RunPeriod = 8; //specify how many consecutive periods should trigger a change

FirstAvg:
Load Avg(Value) as SeedAvg resident Data1 where RecNo() <= $(SeedSample);

Let Seed = peek('SeedAvg');

Drop Table FirstAvg;

let runningAvg = 0;
let runningSum = 0;
let AvgSequence = 0;
let LastAvg = 0;
let seq = 1;
let AvgRun = 0;

//Loop over each row
for r = 1 to NoOfRows('Data1')

let runningAvgPrev = runningAvg;
let runningSum = (runningSum + SubField(FieldValue('UID',$(r)),':',3));
let runningAvg = runningSum / seq;

if runningAvg >= Seed then
let AvgSequence = 1;
else
let AvgSequence = -1;
end if;

if AvgSequencePrev=AvgSequence then
let AvgRun = AvgRun + 1;
ELSE
let AvgRun = 1;
end if;

let AvgSequencePrev = AvgSequence;

if AvgRun = $(RunPeriod) then
let seq = 1;
let runningSum = SubField(FieldValue('UID',$(r)),':',3);
let runningAvg = runningAvgPrev;
let Seed = runningAvg;
let AvgRun = 1;
end if;

let seq = seq + 1;

RunningAvgs:
Load
$(r) as ID,
$(runningSum) as RunningSum,
$(runningAvg) as RunningAvg,
$(AvgSequence) as AvgSequence,
$(AvgRun) as AvgRun,
$(Seed) as ActiveAvg
autogenerate 1;

next r;

Try copying it into a new doc and see how it works.

flipside

Re: Re: Re: Re: QlikView Experts: Control Chart Challenge

Anonymous — Thu, 09 Jan 2014 10:40:58 GMT

Hi,

Enclosed the new data with the Excel-formula.

The first set is givin same result, the second has to be checked.

Did you test flipside's solution ? I'am very curious but I have no time for the moment to test it myself.

Perhaps later on.

flipside, you have already my respect

Paul

Re: QlikView Experts: Control Chart Challenge

Thu, 09 Jan 2014 23:10:30 GMT

Hi Daniel

It is possible in the chart.

If you set 2 more expressions as counters

R2CUMA_revCT: how many rows above untill the next time there is a run of 8

=if(isnull(below([R2CUMA_revCT])),0,

if([R2CUMA]=-8,0,below([R2CUMA_revCT])+1))

R2CUMA_abvCT: how many rows below until the next time there is a run of 8

=if(isnull(above([R2CUMA_abvCT])),0,

if([R2CUMA]=-8,0,above([R2CUMA_abvCT])+1))

Then use these in a rangesum calculation to get the average for "R2CUMA_revCT" rows below and "R2CUMA_abvCT" rows above.

Average2:

=rangesum(above(Data,-R2CUMA_revCT,R2CUMA_revCT+R2CUMA_abvCT))

/RangeCount(above(Data,-R2CUMA_revCT,R2CUMA_revCT+R2CUMA_abvCT))

Example attached. NB: the shift is barely detectable, but still there!

It's not quite there yet, but something to work from!

Erica

Re: QlikView Experts: Control Chart Challenge

Thu, 09 Jan 2014 23:45:11 GMT

You can easily change the counter expressions to reset when they get to points below the line, as well as above:

=if(isnull(below([R2CUMA_revCT])),0,

if([R2CUMA]=-8 or [R2CUM]=-8,0,below([R2CUMA_revCT])+1))

Where R2CUM is the equivalent expression that tests for points below the line.

Erica

Re: Re: Re: Re: Re: QlikView Experts: Control Chart Challenge

danielgargiulo — Fri, 10 Jan 2014 01:17:39 GMT

Hi Paul,

The second sheet is very close to be being perfect. The only thing is that once the rule has been meet it can not be meet again until there has been at lease another 9 points. I have reattached it and highlighted in red where this rule would come into effect. I am not sure how/where we add the rule that says if the average has restarted then needs to be at least 9 points away from when it started but i think once we get it we would have automated it in excel at least.

Thanks again for all your help to date

Re: Re: Re: Re: Re: QlikView Experts: Control Chart Challenge

danielgargiulo — Fri, 10 Jan 2014 01:26:34 GMT

Hi Flipside,

Thanks for responding. I have put your script into a QVW and input the data (see QVW attached). It seems to be re-calculating the average every 8 points (or what ever the 'runperiod' is set to). What we require to trigger a change in the average is 9 (runperiod) consecutive points above the average. I feel like your script and the concept of a loop approach is another step closer with one of the remaining piece of the puzzle for this approach is getting the step up trigger correct.

I look forward to seeing if we crack this.

kind regards,

Dan

Re: Re: Re: Re: Re: QlikView Experts: Control Chart Challenge

danielgargiulo — Fri, 10 Jan 2014 01:48:12 GMT

Thanks Erica. This also sounds like it is a step closer and if we can do it in the chart that would be AMAZING!!! In your example it seems that the average is not what I would have expected though. I would have expected it to be around 91% for starters and then jump to around 94% on the 01/11/13 to the end of the period. This is based on 9 consecutive points. Again i feel like the concept is there and it is just a matter of tweaking to get it right.

I have to head to a wedding now so don't have time to fully understand the new fields sorry but I will hopefully get a chance over the weekend.

thanks

Dan

Re: Re: Re: Re: Re: QlikView Experts: Control Chart Challenge

Fri, 10 Jan 2014 08:53:25 GMT

Hi Dan

It's probably because there issues with the Above function - for some reason rangecount(above(Data,0,30) is returning 17, not 30! I think it because the field is dynamic ie $(variable) rather than the field name... the counters also need to be tweaked to recognise the end of a run rather than just 8 steps into it

Anyway enjoy your weekend, and the wedding

Regards,

Erica

Re: QlikView Experts: Control Chart Challenge

flipside — Fri, 10 Jan 2014 09:25:29 GMT

Hi Dan,

Try changing the line ...

if AvgRun = $(RunPeriod) then

to ..

if AvgRun > $(RunPeriod) then

It just depends whether you want the step up to occur ON the RunPeriod threshold or the period AFTER it.

flipside

Re: Re: Re: Re: Re: QlikView Experts: Control Chart Challenge

danielgargiulo — Sat, 11 Jan 2014 20:30:35 GMT

Hi Flipside,

I have made this change and we are still having the average calculated at regular intervals instead of only when there are 9 consecutive points above the running average.

I think what we are missing is that every loop we need to re compare the previous 9 'Values' to the new 'RunningAvg' to see if they are above it or not. If they are all above the new running average this would trigger a change.

Again i feel like this approach is close.

Thanks,

Dan

Re: QlikView Experts: Control Chart Challenge

Sun, 12 Jan 2014 23:23:16 GMT

Hi Dan

If you change the counters to this:

R2CUMA_revCT

=if(isnull(below([R2CUMA_revCT])),0,

if(([R2CUMA]<=-8 and Below([R2CUMA])=0) or ([R2CUM]<=-8 and Below([R2CUM])=0),

below([R2CUMA_revCT])+1))

R2CUMA_abvCT

=if(isnull(above([R2CUMA_abvCT])),0,

if((above([R2CUMA])<=-8 and [R2CUMA]=0) or (above([R2CUM])<=-8 and [R2CUM]=0),

0,above([R2CUMA_abvCT])+1))

Then they will commence at the end of a run of 8 or more negative/ positive values.

I'm losing the plot with the Range and above/below functions though when it comes to the average. Even just a simple expression rangecount(above(total [Data],-30,30)) is returning strange values. (it should return 30 - because that is the range that is returned by the above function...!)

Any ideas on that anyone?

Erica

Re: QlikView Experts: Control Chart Challenge

Sun, 12 Jan 2014 23:40:14 GMT

Okay - this works, but only as a temporary fix as either I don't fully under stand the above / below functions, or there maybe a bug.

I've replaced the "average 2" formula with a function which uses the below() function if it is at the start of a run (seems to work fine when offset=0) otherwise, steal the value above it!

=if(R2CUMA_abvCT=0,

rangesum(below(Data,0,R2CUMA_revCT+R2CUMA_abvCT))

/(R2CUMA_revCT+R2CUMA_abvCT),

above([Average 2]))

Erica

Re: Re: Re: Re: Re: Re: QlikView Experts: Control Chart Challenge

Anonymous — Mon, 13 Jan 2014 00:50:34 GMT

Hi,

Hope you are still interested in a Excel-solution because I did not follow the QlikView-solution yet ?

The previous Excel was correct, but I had to copy the calculated formulas manualy to 2 columns.

In this new version I used the INDERECT-Excel-function (new to me) so that you only have to give columns A and B and the average will show up in column R (you can hide all the other columns).

I also added 9 columns to test if the data is decreasing, but I think in the current sample there is no such point.

Could you confirm the correct results (and test other series of data) ?

If it's OK, we have a tool to compare it with the QV-results, and I can start looking into it (but I think the others are more skilled in QV than me !!)

Paul