Count distinct based on number of events

[Anonymous]

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.

[MK_QSL]

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]

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]

Attachment is missing !

[Anonymous]

Here is the attachment

[Anonymous]

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]

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]

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])