QlikView Experts: Control Chart Challenge

[danielgargiulo]

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

[#2]

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

[danielgargiulo]

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

[danielgargiulo]

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

[danielgargiulo]

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

[#6]

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

[flipside]

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

[danielgargiulo]

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

[#9]

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

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

[#10]

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

[Anonymous]

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