Summing by groups in beast mode?

deona720
deona720 Member
in Charting

I'm running on 2 hours of sleep so I think I am just overthinking this right now but I essentially have a large dataset of event attendance by members

Here is a sample of my data:

Event Date

Event Name

Member ID

Member is Registered for Event

Parent Group

Location Type

Apr-25

Event 1

1

0

Primary

Primary

Apr-25

Event 1

2

1

Primary

Primary

Apr-25

Event 1

3

0

Primary

Primary

Apr-25

Event 1

4

0

Primary

Primary

Apr-25

Event 1

5

1

Primary

Primary

Sep-24

Event 2

1

1

Primary

Primary

Sep-24

Event 2

2

1

Primary

Primary

Sep-24

Event 2

3

0

Primary

Primary

Sep-24

Event 2

4

1

Primary

Primary

Sep-24

Event 2

5

0

Primary

Primary

Apr-23

Event 3

1

1

Primary

Primary

Apr-23

Event 3

2

1

Primary

Primary

Apr-23

Event 3

3

0

Primary

Primary

Apr-23

Event 3

4

1

Primary

Primary

Apr-23

Event 3

5

1

Primary

Primary

But as you can see the member ID is going to repeat for every single event so its producing 40000 instances instead of 200 out of 1200 possible members for this event and 450 out of 1200 possible members for this event didnt attend.

I know I can pivot table this to count how many are 0, but is there a way to incorporate this into metrics too? Im hoping to create this in a card level since its coming from a federated source

Or like these metrics/table

People who have never attended an event

1

or

People who havent attended an event in the last year

3

or

Event Name

People that did not attend but could have

People that attended

Event 1

3

2

Event 2

2

3

Event 3

1

4

Tagged:

Best Answer

  • ggenovese
    ggenovese Coach
    Answer ✓

    Hi @deona720

    In order to solve this I'd recommend creating the following beast modes:

    • Member Count
    COUNT(DISTINCT `Member ID`)
    • People That Attended
    IFNULL(COUNT(DISTINCT CASE WHEN `Member Is Registered for Event` = 1 THEN `Member ID` END ),0)
    • People Who Have Never Attended an Event 
    — This beast mode subtracts People That Attended from Member Count to get the count of non-attendees
`Member Count` - `People That Attended`

    And then you can use these beast modes to answer your questions:

    image.png image.png image.png

Answers

  • ggenovese
    ggenovese Coach
    Answer ✓

    Hi @deona720

    In order to solve this I'd recommend creating the following beast modes:

    • Member Count
    COUNT(DISTINCT `Member ID`)
    • People That Attended
    IFNULL(COUNT(DISTINCT CASE WHEN `Member Is Registered for Event` = 1 THEN `Member ID` END ),0)
    • People Who Have Never Attended an Event 
    — This beast mode subtracts People That Attended from Member Count to get the count of non-attendees
`Member Count` - `People That Attended`

    And then you can use these beast modes to answer your questions:

    image.png image.png image.png

Categories