We’re asking you to select a content label when starting a new topic in the Discussions area. Read more to find out why.
Choose Language Hide Translation Bar
Highlighted
Level II

## Wanting to count which hours are used in a day, not repetitions in the data

So I have a 30+million row chart in which I have the data segmented like this (this is a mock up of the format):

:

Where I have a variable I'm counting ('Number'), a group, a full date/time string, the date only, the hour (out of 24 hours), the total variable count ('Number') per day, and the hours active per day. What I'm trying to do is determine the number of hours a GroupID is active per day. In the hours per day column, I have the following formula:

So if we see from the table, Im getting a result of 5 hours active, not the 3 unique hour counts of the hour that I want (for 10am, 11am and 12pm). It is taking into account repetitions, and what I'd like to find is the unique number of instances of that hour, per day, per GroupID.

Any help would be appreciated, thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: Wanting to count which hours are used in a day, not repetitions in the data

You need to check that it's the first one.

With 30M+ rows this may take a bit to run, but it should get you what you want.

``Col Sum( Col Cumulative Sum( 1, :Date Only, :GroupID, :Hour ) == 1, :Date Only, :GroupID )``

It basically looks that the cumulative sum of the group, date, hour combo == 1.  If it isn't, returns a 0.  Then you just col sum() by the date/group

Vince Faller - Predictum
2 REPLIES 2
Highlighted
Super User

## Re: Wanting to count which hours are used in a day, not repetitions in the data

You need to check that it's the first one.

With 30M+ rows this may take a bit to run, but it should get you what you want.

``Col Sum( Col Cumulative Sum( 1, :Date Only, :GroupID, :Hour ) == 1, :Date Only, :GroupID )``

It basically looks that the cumulative sum of the group, date, hour combo == 1.  If it isn't, returns a 0.  Then you just col sum() by the date/group

Vince Faller - Predictum
Highlighted
Level II

## Re: Wanting to count which hours are used in a day, not repetitions in the data

This is perfect, thank you so much!