Magic ETL

Magic ETL

Cumulative Count by Month

I am looking to obtain a count of all dates that are less than each month on a table. Please see attachment for reference. In other words, I need a cumulative count of how many enrollments the company has so far going into each month.

 

The 2019-Dec line should count all dates less than 12/1/2019

The 2020-Jan line should count all dates less than 1/1/2020

The 2020-Feb line should count all dates less than 2/1/2020

and so on...

 

Is there some way to dynamically reference the date of each line? Almost like curdate, but the date of each line instead.

 

I hope I have explained this adequately. Thank you in advance for any help I receive.

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

Comments

  • You could use a Running Total card to automatically build the cumulative totals for you. You could also create a beast mode called DisplayDate (or whatever makes sense to you) that just has a AddDate function in it that would display the next months name instead of the month it is in. This function would do it

    1. ADDDATE(`Date Entrolled`, interval 1 month)

    If you don't want to use a Running Total card, you would need to use the Rank & Window Functions in Magic ETL to build the cumulative total.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • If you have window functions enabled in your instance you could do this via a windowed function in a Beast Mode instead of a running total card:

    1. SUM(SUM(1)) OVER (ORDER BY `Date`)

    And then utilize @MarkSnodgrass 's solution about adding a month to the date for display purposes via another beast mode.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • @GrantSmith wrote:

    If you have window functions enabled in your instance you could do this via a windowed function in a Beast Mode instead of a running total card:

    1. SUM(SUM(1)) OVER (ORDER BY `Date`)

    And then utilize @MarkSnodgrass 's solution about adding a month to the date for display purposes via another beast mode.


    If you want to count by month then just alter @GrantSmith  SQL:

     

    SUM(SUM(1)) OVER (GROUP BY date_format(`DATE`, '%Y%m') ORDER BY `Date`)

    In order for it to work, you'll need to have year-month on the axis.  

     

    This technique we're introducing is called 'Window Functions' check out the video below for more information!

    https://www.youtube.com/watch?v=cnc6gMKZ9R8&t=316s

     

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Dear @jaeW_at_Onyx , how do i know if Windows Function is enable on my instance ?

  • @user025461 , contact your Domo CSM or sales representative and ask them to enable the feautre: "window functions in beast mode"

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

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