Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Wanting to count which hours are used in a day, not repetitions in the data

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 6, 2019 12:53 PM
(1529 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

This is perfect, thank you so much!