Skip to content

    • Magic ETL

    Magic ETL

    Total the count of Attendees for last 7 days & last 14 days in Table View

    Member
    in Magic ETL

    I am looking to find a way in Beast mode to do the following

     

    I have CustID and Class_Date and Class_Time.  I am trying to show total Attendees and % of total for last 7 days and last 14 days both displaying in Table view mode.  Any thoughts on how to do thiss via Beast Mode or any other way?

    Best Answer

    • Contributor
      Answer ✓

      Some variation of the following should work for you.

       

      Total Attendees: No beast mode necessary, just do a COUNT aggregation on any column (I used CustID)

       

      Last 7 Days %:

      SUM((CASE
      WHEN DATEDIFF(CURDATE(),`Class_Date`) <= 7 THEN 1
      ELSE 0
      END
      ))

      /

      COUNT(`CustID`)

       

      Last 14 Days %:

      SUM((CASE
      WHEN DATEDIFF(CURDATE(),`Class_Date`) <= 14 THEN 1
      ELSE 0
      END
      ))

      /

      COUNT(`CustID`)

       

       

      Test data:

      Dojo Help 601.JPG

       

      Results:

      Dojo Help 602.JPG


      **Make sure to like any users posts that helped you and accept the ones who solved your issue.**

    Answers

    • Contributor
      Answer ✓

      Some variation of the following should work for you.

       

      Total Attendees: No beast mode necessary, just do a COUNT aggregation on any column (I used CustID)

       

      Last 7 Days %:

      SUM((CASE
      WHEN DATEDIFF(CURDATE(),`Class_Date`) <= 7 THEN 1
      ELSE 0
      END
      ))

      /

      COUNT(`CustID`)

       

      Last 14 Days %:

      SUM((CASE
      WHEN DATEDIFF(CURDATE(),`Class_Date`) <= 14 THEN 1
      ELSE 0
      END
      ))

      /

      COUNT(`CustID`)

       

       

      Test data:

      Dojo Help 601.JPG

       

      Results:

      Dojo Help 602.JPG


      **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
    • Member

      Thanks, I needed something similar but it worked.  I needed to Sum the total Customers for the last 7 & 14 days separately.  However there are many classes in a day and I had to modify it slightly to work.  But it gave me most of the answer...thank you! 

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories