Beast mode - Calculate how many rows per individual

user16684
user16684 Member
in Magic ETL

Is there a way for me to create a beast mode calculation to return how many rows there are for each individual?

 

For example, the data would have:

 

Employee ID             Employee                   Training Completed                         Completion Date

123                           Joe                               Welcome to Company ABC             2/5/19

123                           Joe                               How to use DOMO                           3/9/19

222                           Mary                             Welcome to Company ABC             4/8/19

 

The beast mode calculation would output that Joe = 2 and Mary = 1. In the end, I want to be able to display how many employees have completed 0 trainings, 1-3 trainings, or more than 3 trainings.

 

 

Thank you!

Comments

  • Sambo_Servicer
    Sambo_Servicer Member

    Hey there,

     

    Sounds like you need a count of completed courses and then categorization for the "How many courses have been completed."

    Maybe something like this will help:

     

    Case

                 when sum(Case when `Completion` is not null then 1 end) =0 then '0'

                 when sum(Case when `Completion` is not null then 1 end) ❤️ then '1-3'

                 when sum(Case when `Completion` is not null then 1 end) >0 then '3+'

    END

     

    Props on using coursebuilder, btw.

  • user16684
    user16684 Member

    I tried that but it only returns '+3' for all of the data. I need it to find how many trainings has each individual completed. Does that make sense?

     

    In my fake data example, I would need to show that Joe has completed 2 and Mary has completed 1 so I have 1 user that has completed 2 and 1 that has completed 1.

     

    Thanks for your help!

    @Sambo_Servicer wrote:

    Hey there,

     

    Sounds like you need a count of completed courses and then categorization for the "How many courses have been completed."

    Maybe something like this will help:

     

    Case

                 when sum(Case when `Completion` is not null then 1 end) =0 then '0'

                 when sum(Case when `Completion` is not null then 1 end) ❤️ then '1-3'

                 when sum(Case when `Completion` is not null then 1 end) >0 then '3+'

    END

     

    Props on using coursebuilder, btw.

     

  • Sambo_Servicer
    Sambo_Servicer Member

    Sorry. Typo on my end.

     

     

    Case

                 when sum(Case when `Completion` is not null then 1 end) =0 then '0'

                 when sum(Case when `Completion` is not null then 1 end) ❤️ then '1-3'

                 when sum(Case when `Completion` is not null then 1 end) >=3 then '3+'

    END

Categories