Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Sept. 27, 10AM ET Do more with Qlik, Insight Advisor – our intelligent AI-assistant in Qlik Sense: **Register**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- QlikView Experts: Control Chart Challenge

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

danielgargiulo

Partner - Creator

2014-01-07
12:55 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

QlikView Experts: Control Chart Challenge

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

- « Previous Replies
- Next Replies »

31 Replies

Not applicable

2014-01-08
06:31 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Daniel

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

Erica

2,313 Views

Anonymous

Not applicable

2014-01-08
06:49 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2,315 Views

danielgargiulo

Partner - Creator

2014-01-08
02:25 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2,315 Views

Anonymous

Not applicable

2014-01-08
07:10 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2,315 Views

danielgargiulo

Partner - Creator

2014-01-08
07:48 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2,315 Views

danielgargiulo

Partner - Creator

2014-01-08
07:48 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2,313 Views

flipside

Partner - Specialist II

2014-01-09
04:16 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

FirstAvg:

//Loop over each row

RunningAvgs:

autogenerate 1;

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

flipside

2,315 Views

Anonymous

Not applicable

2014-01-09
05:40 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2,315 Views

Not applicable

2014-01-09
06:10 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2,315 Views

- « Previous Replies
- Next Replies »