Skip to content

    • Magic ETL

    Magic ETL

    Best mode - combine MTD and YTD

    Member
    in Magic ETL

    Hi there!

    I have a problem with beast mode. I think the solution is easy, but I can´t figure out the correct function in beast mode.

     

    We need to display sales of our team. Therefore I created a table with team names and sales MTD and Sales YTD.

     

    My datasource includes a field called season (our business year is from May till April next year) which I use for my Sales YTD.

     

    Unfortunately, I can´t figure out the MTD part, which is always based on the current month.

     

    I tried this function in beast mode:

    1. (CASE
       when MONTH(`Datum`)=MONTH(CURRENT_DATE()) and YEAR(`Datum`)=YEAR(CURRENT_DATE()) then 
       SUM((CASE when `Total` is null then 0 else `Total` end))
       else 0
       end)

     

    In return I always get 0 per person. If I add "this month" in my card builder the function works, but my YTD doesn´t anymore.

     

    Do I need to add something in my MTD formula, because I think I have an issue with identifying dates (maybe?).

     

    I wasn´t able to find anything close in other thread.

     

    Many thanks for helping me!

     

    BR

     

    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

    Best Answer

    • Domo Employee
      Answer ✓

      I would try pulling the aggregate out of the case statement.

       

      sum(CASE
      when MONTH(`Datum`)=MONTH(CURRENT_DATE()) and YEAR(`Datum`)=YEAR(CURRENT_DATE()) then
      ifnull(`Total`,0)
      else 0
      end)

    Answers

    • Domo Employee
      Answer ✓

      I would try pulling the aggregate out of the case statement.

       

      sum(CASE
      when MONTH(`Datum`)=MONTH(CURRENT_DATE()) and YEAR(`Datum`)=YEAR(CURRENT_DATE()) then
      ifnull(`Total`,0)
      else 0
      end)

    • Unknown
      Unknown

      Thank you for your help Superman!!

       

      I don´t really understand why my function didn´t work out, but yours did!

       

       

    • Domo Employee

      A case statement works through your data one row at a time.  It looks for the first "when" statement that is correct and then evaluates the "then" expression.  If no when statement is true then it goes to an "else" statement or returns a null value.

       

      In your case, you had one when clause and an else statment.  So, when it evaluated the expression, the first row of data did not meet the when clause (month and year) so it went ahead to the else statment and returned 0.

       

      by pulling the aggregate sum() function outside of the case statement, you are telling the case statement to continue running for all rows of data and then sum all of the results.

       

      Hope that helps.  Let me know if you have more questions.

    • Unknown
      Unknown

      Thanks for helping me out!

       

      I think I understand your 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

    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