Counting Units Over Time

[johnca]

I've looked at several posts looking at counting instances and mine seems unique. It pertains to units messaging (aka "On Air")and I need to identify units that repeatedly message over a period of time including the latest time period (current). Query periodicity is weekly. Each query fetching unit data returns data such as serial number and last message time, plus customer and other info. Most units message multiple times per day but I only fetch the latest each query. I suspect I could count the times a unit shows up over a known period of time and count that number of queries and if the counts match plus it exists in the latest query that would give me the number of current continually messaging units. I also suspect it would work dynamically where if I open the period to say 10 periods and the unit messaged 10 times and existed in the latest period would also exist in all periods under 10. I think it can all be done in set analysis and no extra aggregation in the scripts.

Has anyone done something like that before and can say whether this is the simplest method or would there be another method better suited?

V/r,

John

[johnca · Accepted solution]

So, I created it almost exactly as I predicted it might work. I compared the counts of each serial number against the count of file dates and it does what I need. The pivot shows just those serial numbers where these two counts are equal. Serial numbers that do not meet the criteria do not in the table.

If(count({<[On Air]={'Yes'}>}Total <SERIAL_NUMBER> [On Air])=Count(Distinct Total FileDate),
count({<[On Air]={'Yes'}>}Total <SERIAL_NUMBER> [On Air]))