MySQL Data Flow - Running Total - Specific Period?

nlonkai
nlonkai Member
in Magic ETL

Hi there!

I am currently working on a running total, which worked out fine. The only thing I can´t figure out is to calculate a running total per specific period.

My table should look like that:

image.png

 As you can see our season starts on the first of May and ends on the last of April in the following year.

 

My current code for YTD

 

1) (table: ytd_figures)

SELECT a1.created_at_trans, a1.platform,(SELECT SUM(a2.total)
FROM proc_group a2
WHERE a2.platform = a1.platform AND a2.created_at_trans <= a1.created_at_trans)
AS ytd_total
FROM proc_group a1
ORDER BY a1.created_at_trans, a1.platform;

 

2) (table: proc_group_ytd)

SELECT n.*, n1.YTD_total
From proc_group n LEFT JOIN ytd_figures n1 ON n.created_at_trans = n1.created_at_trans
and n1.platform=n.platform

 

Could someone please show me where I need to include a statement for determining the time period?

 

Addional question: Is there any chance to include a window function, because they seem to be more efficient.

 

Thanks

 

 

Best Answer

Answers

  • Domo_Diesel
    Domo_Diesel Domo Employee

    Hi @nlonkai ,

     

    Happy to help figure this out.  This would be relatively easy to solve using a window function.  Do you have access to Redshift dataflows in your Domo instance?  If not, I would recommend asking your Domo Account Executive for access.  Redshift dataflows allow you to use window functions.  

     

    Here is how I would recommend doing this in a Redshift dataflow:

     

    1. Create a new column to order the dates by.  This is easy to do if you have a fiscal calendar already loaded into Domo.  Day 1 of the year would be 5/1, while day 365 would be 4/30.  Let's call this "Date_Order".      
    2. Shift the date forward by 1 year for all dates that fall between 5/1 and 12/31.  E.g., 5/1/2016 becomes 5/1/2017.  
    3. Now, you are ready to use a SUM window function: 

    select "date", "total", sum("total") over (partition by date_part(year, "date") order by "Date_Order"

    unbounded preceding) as sum from your_table order by "date"

     

    This example of a window function allows you to accomplish the piece you are missing, which is partitioning the data by your fiscal year.  This is the part of the example code that says, "partition by date_part(year, "date")"

     

    Let me know if this makes sense, and if you have any other questions about this.  This could be done in MYSQL as well, but it would be a lot more complex, and may not be as scalable.  For these reasons, I recommend going with a Redshift dataflow if you can.

  • n8isjack-ret
    n8isjack-ret Domo Employee
    Answer ✓

    @nlonkai you can also do window functions in Magic ETL as well. There is an article in the knowledge base that goes over the steps: https://knowledge.domo.com/?cid=etlactionsrankandwindow

    Former Domo employee you can find me in the Dojo Community here @n8isjack
  • nlonkai
    nlonkai Member

    sorry for my late answer. We had issues with our Domain.

     

    It worked out perfectly (and simple) with ETL Ranking and Window.

     

    Thanks

     

Categories