BookmarkSubscribe
Choose Language Hide Translation Bar
chall
Occasional Contributor

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

image.png:

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: 

image.png

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!

 

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
vince_faller
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
vince_faller
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
chall
Occasional Contributor

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

This is perfect, thank you so much!

0 Kudos