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

Count distinct based on number of events

Hi Qlik Community,

I need to create a chart and table where I need to count number of terminals that are bouncing (out of network or coming in to the network for very short time) and also another count based on different criteria, which is based on number of events (e.g. number of events where terminal is out of network within observation period).

The key information in my table for this is as follows:

Time Stamp, Terminal Id, Network status

Time stamp periodicity is every 5 minutes (each row shows 5 minutes worth of information)

The content of network status is IN_NETWORK or OUT of NETWORK, with some additional information (text) indicating how long the terminal is in the network.

I'd like to count:

  1. Number of terminals which are bouncing (changing from in-network to out of network or vice versa within certain duration, e.g. 2 or 3 consecutive time stamp samples). Please note each row in the table is 5 minutes data, so 2 consecutive samples where terminal is out of network is 10 minutes.
  2. Number of  terminals which are out of network for 5 minutes ( 1 time stamp sample), 10 minutes ( 2 time stamp samples) and 15 minutes (3 time stamp samples), etc.  This does not need to be with the criteria as above (changing status from in to out or vice versa for certain duration)
  3. Table showing for each terminal number of times where it is out of network more than 10  minutes (e.g. 2 time stamp samples).


The dimension will be Time stamp and the expression will be the above count. 

What would be the right expression for the above?

Appreciate any help on this.

18 Replies
MK_QSL
MVP
MVP

It would be better if you have provided few lines of sample data along with required output in (may be) excel file.

It is difficult to work without data or prepare data ourselves to work.

Anonymous
Not applicable
Author

Hi,

Please find attached a sample file. Please note that in this file, out of network is indicated by a text that contains word "UNDETECTED". The terminal status is the right name as opposed to network status in the original posting.  This first 3 columns are the area of interest.

MK_QSL
MVP
MVP

Attachment is missing !

Anonymous
Not applicable
Author

Here is the attachment

Anonymous
Not applicable
Author

My immediate need is to count for the case no 2 above; Number of  terminals which are out of network for 5 minutes ( 1 time stamp sample), 10 minutes ( 2 time stamp samples) and 15 minutes (3 time stamp samples), etc.


I tried this expression for 2 time stamp samples but did not display anything.


count(DISTINCT{<[Terminal Age]={'=count("*UNDETECTED*")>2'}>}([Terminal Name]))


Any suggestion?

Anonymous
Not applicable
Author

Terminal Age above is actually terminal status column in my sample file.

Digvijay_Singh

You should use it like this -

=count(DISTINCT{<[Terminal Status]={"=count([Terminal Status]='*UNDETECTED*')>2"}>}[Terminal Id])

But confused when I found that no terminal ID in your sample data is having more than one timestamp value, so couldn't understand how terminal ID count can be more than 1 UNDETECTED for more than 5 mins.

But your above expression give positive result so I am trying to understand logic.

Anonymous
Not applicable
Author

Hi Digvijay,

Thank you for the correction of the syntax in my expression.

Some of filed names are not correct in my expression above as I used different filed names in my real data (sorry, forgot to change it for the example file), but the idea is basically to count number of terminals that has undetected for more than 5 minutes, or 10 minutes or etc. (this can be variable inputs).

Each time stamp is 5 minutes interval, so the intention is simply to count how many distinct terminals shows undetected more than let's say 10 minutes (which is associated with 2 time stamp samples)  over period of time (e.g. every 30 minutes, or 1 hour or a day).

I tried =count(DISTINCT{<[Terminal Status]={"=count([Terminal Status]='*UNDETECTED*')>2"}>}[Terminal Id]) in my sample file and something similar in my real data and now it is showing something but not sure if the chart is even correct as the number seems hig.

Any suggestion?

sunny_talwar

May be this:

=Count(DISTINCT{<[Terminal Id] = {"=Count({<[Terminal Status] = {'*UNDETECTED*'}>} [Terminal Id]) > 2"}>} [Terminal Id])