Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Upper and lower bound limit

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.

34 Replies
Anonymous
Not applicable
Author

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?

sunny_talwar

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

Anonymous
Not applicable
Author

It looks like still incorrect expression for the above

sunny_talwar

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

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

Anonymous
Not applicable
Author

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?

sunny_talwar

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?

Anonymous
Not applicable
Author

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)

sunny_talwar

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

Anonymous
Not applicable
Author

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

Capture.JPG

sunny_talwar

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