topic SPC Highlights in QlikView

SPC Highlights

steveniow — Wed, 25 Nov 2020 16:16:04 GMT

HI,

I have been trying to follow a very useful post from Erica Whalley at

QlikFit: Qlikview Lean tips #1: How to highlight series of points that violate control rules on a chart

I have run into a problem though.

this expressions requires an average, which I want to be dynamic.

=if(RowNo()=1 or count(Key)>([Average]),0,above(R2CUM)+(count(Key)<=(Average]))

I have tried using the average expression

avg(aggr(Count(Key),Week))

but of course this just returns the count(Key) because it is averaging and aggregating it over the one week in the chart.

the only way around this I can find is to calculate the average manually as below

=if(RowNo()=1 or count(Key)>(count(total Key)/NoOfRows()),0,above(R2CUM)+(count(Key)<=(count(total Key)/NoOfRows())))

which is fine for highlighting points above/below the mean but I also want to highlight above or below the UCL and LCL.

so is there a way around this problem please?

thanks once again for your time.

Kind Regards

Steve

Re: SPC Highlights

prma7799 — Fri, 02 Jun 2017 10:58:11 GMT

Hi Steven,

stalwar1‌ , tresesco‌ will help you on this ...

Thanks

Re: SPC Highlights

sunny_talwar — Fri, 02 Jun 2017 11:04:50 GMT

Would you be able to share a sample where we can see the issue?

Re: SPC Highlights

steveniow — Tue, 13 Jun 2017 14:57:21 GMT

hi Sunny, sorry it has taken a while to respond - I have been off.

I can't see how to share the document?

Steve

Re: SPC Highlights

sunny_talwar — Tue, 13 Jun 2017 15:00:04 GMT

Check this out

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Re: SPC Highlights

steveniow — Tue, 13 Jun 2017 15:12:23 GMT

Thanks Sunny - I was trying to reply through the inbox - hence the problem!

The formula in question is R2CUM. it works for highlighting points above and below the average but I have had to manually calculate the average as you will see in the expression. The problem is I want to do the same thing for over the UCL or under the LCL - which I can't do from a manual expression -hence I have a problem...

Kind Regards

Steve

Re: SPC Highlights

sunny_talwar — Tue, 13 Jun 2017 15:15:44 GMT

Would you be able to expand what do you mean by manual expression?

Re: SPC Highlights

steveniow — Tue, 13 Jun 2017 15:20:23 GMT

The average is being calculated from

count(total Key)/NoOfRows()

rather than by using an expression from avg()

this means I cannot use stdev() to adapt the expression to highlight above and below UCL or LCL.

Re: SPC Highlights

sunny_talwar — Tue, 13 Jun 2017 15:23:39 GMT

You can calculate average like this

Avg(TOTAL Aggr(Count(Key), Week))

and stdev like this

Stdev(TOTAL Aggr(Count(Key), Week))

Re: SPC Highlights

steveniow — Tue, 13 Jun 2017 15:28:57 GMT

ahhh... is it the TOTAL that makes a difference? I tried it without the TOTAL and it returned count(Key)?

Brilliant - really appreciate your help - thank you.

Re: SPC Highlights

sunny_talwar — Tue, 13 Jun 2017 15:30:26 GMT

It does.... TOTAL is used to get the Avg regardless of the dimension... same is true for Stdev

Re: SPC Highlights

steveniow — Tue, 13 Jun 2017 15:31:29 GMT

Many thanks