Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Please find attached data sample in the original post
I was hoping to see a qvw file where you already have created the variable and chart. Would it be possible to share that?
Unfortunately the real data with qvw file contains sensitive information that I can't publish in public. I'll see if I can generate similar output with different data. It seems sample file is not showing the expected pattern as I posted.
Not sure if you have already seen this, but this thread can address your sensitive information issues
Preparing examples for Upload - Reduction and Data Scrambling
Hi Sunny,
Please find attached the qvw file. Hope you an play with it and suggest the solution
Is this the issue?
Yes, that is the issue. What is missing from my expression
I think that's how QlikView will display AKAIK. May be marcowedel can offer some advice here.
Best,
Sunny
Hi Sunny, how did you manage to get that chart displayed correctly on your
end?
Anything wrong with the expression that I used?
Is this what you wanted? I thought this was not right. I think you need to use your color expression in the background color and not as another expression