topic Re: Upper and lower bound limit in QlikView

Upper and lower bound limit

Anonymous — Wed, 07 Sep 2016 23:34:43 GMT

Hi Qlikview experts,

I have set of data several days worth of data with millions of records that have time stamps (e.g. every minute of 5 minute intervals) and several columns containing various information.

To simplify, the columns of interest are:

Time Stamp, User Name, network id, user status, region, throughput

Network status value is either IN or OUT

I am trying to create a chart showing number of distinct users accessing the network over the time (e.g. on hourly basis) or with variable interval and plotting upper and lower bound limit (w/ 95% confidence interval). In this case user status will need to be IN.

What I did was

Dimension = Hour (Time Stamp)

Expression is

count({<[user_status]={'*IN*'}>}distinct([User Name]))

While I can simply show the above expression in the chart, the lower and upper limit w/95% confidence is not properly shown.

Any suggestion how this should be done. Is there any aggregation function that should to be used here?

If I wanted to do the same thing for throughput (e.g. using sum(throughput)), will it be the same expression to be used?

Showing

Also I'd like to be able to show the same thing when changing the dimension to a calculated dimension like below

Timestamp((Ceil([Time Stamp],1/IntervalSize)),'DD-MMM-hh:mm')

The interval size for various period (15 min, 30 min , 1 hour, etc.) is predefined in the loading script.

Re: Upper and lower bound limit

sunny_talwar — Wed, 07 Sep 2016 23:52:09 GMT

Would you be able to provide a sample with what you are hoping to see

Re: Upper and lower bound limit

Anonymous — Thu, 08 Sep 2016 00:20:20 GMT

Hi Sunny, please find attached the sample file.

What i'd like to see is something like this

Re: Upper and lower bound limit

MarcoWedel — Thu, 08 Sep 2016 00:49:23 GMT

Hi,

maybe helpful:

Control Chart

regards

Marco

Re: Upper and lower bound limit

Anonymous — Sun, 11 Sep 2016 14:51:49 GMT

Hi Marco,

Thanks for the link. I followed the instruction in the link and plugged the numbers using my data but they are not showing the charts I am looking for. I can't show average, upper and lower bound lines using the suggested expression.

My daily expression for my data is count(DISTINCT{<[network status]={'*IN_NETWORK*'}>}([User Name]))

and my dimension is Timestamp((Ceil([Time Stamp],1/IntervalSize)),'DD-MMM-hh:mm')

What would be the correct expression for the average as Avg(Aggr(count( or Avg(Aggr(sum etc. are not working. Any suggestion or hint?

Thank you,

Fikri

Re: Upper and lower bound limit

sunny_talwar — Sun, 11 Sep 2016 15:05:54 GMT

For average, may be this:

Avg(TOTAL Count(DISTINCT{<[network status]={'*IN_NETWORK*'}>}([User Name]), [Time Stamp]))

Re: Upper and lower bound limit

Anil_Babu_Samineni — Sun, 11 Sep 2016 17:51:39 GMT

How about SPC chart. Can you please provide your application

Re: Upper and lower bound limit

Anonymous — Mon, 12 Sep 2016 16:08:37 GMT

Tried that expression but it is showing error in expression. So, I think

it needs different expression?

On Sun, Sep 11, 2016 at 12:52 PM, Anil Babu <qcwebmaster@qlikview.com>

Re: Upper and lower bound limit

Anonymous — Tue, 13 Sep 2016 12:19:57 GMT

Hi Anil,

The data sample is in the attachment. I tried the expression that Sunny suggested but it said wrong expression, so I can't move forward to create the SPC chart. Do you have any suggestion?

Thanks!

Re: Upper and lower bound limit

sunny_talwar — Tue, 13 Sep 2016 12:29:54 GMT

I think I missed Aggr() function. Can you try this now

Avg(TOTAL Aggr(Count(DISTINCT{<[network status]={'*IN_NETWORK*'}>}([User Name]), [Time Stamp]))

Re: Upper and lower bound limit

Anonymous — Tue, 13 Sep 2016 12:55:43 GMT

Tried the above, Qlikview accepted the expression by showing expression OK with red small mark at the end of character, which does not seem to be caused by any missing parenthesis or spelling.

Still nothing appeared on the chart when added this expression to the reference line. Does it need to remove distinct in this case?

Re: Upper and lower bound limit

sunny_talwar — Tue, 13 Sep 2016 12:58:55 GMT

Can you show a screenshot to show where you are seeing an error?

Re: Upper and lower bound limit

Anonymous — Tue, 13 Sep 2016 13:02:19 GMT

It looks like still incorrect expression for the above

Re: Upper and lower bound limit

sunny_talwar — Tue, 13 Sep 2016 13:04:21 GMT

Try this (I saw one extra parenthesis before User Name:

Avg(TOTAL Aggr(Count(DISTINCT{<[network status]={'*IN_NETWORK*'}>} [User Name]), [Time Stamp]))

Re: Upper and lower bound limit

Anonymous — Tue, 13 Sep 2016 16:53:08 GMT

Sunny,

It is now working. Many Thanks!

The only remaining and last part that I can't get it to work properly is the expression showing symbols with different color when the value exceeds the control chart. I checked the values for Stdev and both upper and lower limits are correct.

I used this expression:

if(count(DISTINCT{<[network status]={'*IN_NETWORK*'}>}([User Name]))>vLCL and count(DISTINCT{<[network status]={'*IN_NETWORK*'}>}([User Name]))<vUCL,lightgreen(),LightRed())

Anything wrong with this expression?

Re: Upper and lower bound limit

sunny_talwar — Tue, 13 Sep 2016 17:06:47 GMT

Only think to check would be the values for vLCL and vUCL. Have you added them to the chart and checked what values do the show?

Re: Upper and lower bound limit

Anonymous — Tue, 13 Sep 2016 18:21:10 GMT

Actually I did and they are showing correctly in reference lines also when

tried in a separate expression. The values for vUCL and vLCL show around

188 and 160 respectively where the distinct count is correctly laid out,

but when creating a separate symbol chart with these 2 variables in one

expression they are not showing up in the charts and simply showing a line

in totally different value. Anything wrong in my expression?

Trying with line chart also shows the same thing. Is it because Qlikview is

unable to show many symbols or a line that has different color?

A few hours of data exceeding the limit line could be quite a few symbols

in the chart)

Re: Upper and lower bound limit

sunny_talwar — Tue, 13 Sep 2016 19:07:00 GMT

Can you try this $(vUCL) and $(vLCL)? Also, if this doesn't work, may be add an equal sign in your variable definition and then try vUCL or $(vUCL). I hate to say this, but variables are very confusing for me and I can never memorize what will work and what won't. Read here:

The Little Equals Sign

The Magic of Variables

Re: Upper and lower bound limit

Anonymous — Wed, 14 Sep 2016 00:46:06 GMT

Hi Sunny,

Thanks for suggestion. I wish this has resolved the problem entirely but it seems not 100%. I hate to say this but nothing changed even after applying the $ and equal sign in variable definitions. It is good enough for now, but it will be better if I can apply the color change when the lines exceed the UCL limit. This is the best I can get so far

Re: Upper and lower bound limit

sunny_talwar — Wed, 14 Sep 2016 01:04:16 GMT

Would you be able to share a sample to look at this? I am not fully sure how you have created those variables and using them. May be playing around with it gives me an idea of how this can be done