Count Number of Log Ins this Year (2019)

SusieQ
SusieQ Member
in Magic ETL

I would like to create an insight card that divides the number of Last Logins in 2019 (125) against my total users (214) to show total percentage of users this year (58%). I am attempting to show this through a chart type of Progress Bar, Filled or Compartive Gauge (any suggestions appreciated).

 

However before I can create this, I need to use Beast Mode to adjust the Last Login column which is a date field that goes back to 2016. I need a formula that can count the Last Login dates that have a year of 2019.

 

I have tried in Beast Mode both a statement and a case, and neither is working.

 

Statement

COUNT(`Last Login`, YEAR(2019))

 

Case Statement:

(CASE when `Last Login`>= YEAR(2019) then COUNT(`Last Login`) end)

 

Any ideas how I can get it to count the 2019 dates? Thanks.

Tagged:

Best Answer

  • Unknown
    Unknown
    Answer ✓

    Hi, @SusieQ,

     

    The case statement approach makes sense to me. That said, let me recommend a couple of changes.

     

    First, the case statement operates at the row level. What you want to do is determine which rows pertain to 2019, and then count those. Therefore, you want the count() outside the case. Second, the year() function operates by returing the year portion of an inputted date. Making adjustments for those two things, try something like this:

    COUNT(CASE WHEN YEAR(`Last Login`) = 2019 THEN `Last Login` END)

    I hope that works for you.

     

    You could also make the year logic dynamic such that it always returns the last login for the current year. That way, you wouldn't need to change your logic when year 2020 rolls around. If that is of interest, check out the current_date() function.

     

    Best,

    Dan

Answers

  • Unknown
    Unknown
    Answer ✓

    Hi, @SusieQ,

     

    The case statement approach makes sense to me. That said, let me recommend a couple of changes.

     

    First, the case statement operates at the row level. What you want to do is determine which rows pertain to 2019, and then count those. Therefore, you want the count() outside the case. Second, the year() function operates by returing the year portion of an inputted date. Making adjustments for those two things, try something like this:

    COUNT(CASE WHEN YEAR(`Last Login`) = 2019 THEN `Last Login` END)

    I hope that works for you.

     

    You could also make the year logic dynamic such that it always returns the last login for the current year. That way, you wouldn't need to change your logic when year 2020 rolls around. If that is of interest, check out the current_date() function.

     

    Best,

    Dan

  • SusieQ
    SusieQ Member

    This worked! Brilliant. Thank you!

    Also, I took your suggestion and used the current date. For anyone who is reading this post, the solution for me was:

     

    COUNT(case when YEAR(`Last Login`) = year(CURRENT_DATE()) then `Last Login` end)

    I used the Progress Bar chart type and got the results needed.

Categories