Skip to content

    • Magic ETL

    Magic ETL

    Summing up sales for previous year

    Member
    in Magic ETL

    Hello -

     

    I'm trying to pull into a table on my card the following:

     

    Current Year Sales

    Previous Year Sales

     

    The calculation I'm using for previous year sales in beast mode is 

     

    CASE
    WHEN YEAR(current_date()) = YEAR(`week_beginning`) - 1
    THEN SUM(`sales`)
    ELSE 0
    END

     

    or

     

    sum(CASE
    WHEN YEAR(current_date()) = YEAR(`week_beginning`) - 1
    THEN `sales`
    ELSE 0
    END)

     

    but I'm getting a value of 0 for previous year sales.

     

    The data is aggregated weekly data we receive from a partner.

     

    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

    Comments

    • Contributor

      I think the likely solution is that on the card itself, you need to specify the date range to include the previous 2 years, like pictured in the attached.

       

      Let me know if that helps.

       

       

    • Domo Employee

      I think that you are subtracting 1 from the wrong year field:

       

      try this.

       

      sum(CASE
      WHEN YEAR(current_date())-1 = YEAR(`week_beginning`)
      THEN `sales`
      ELSE 0
      END)

    • Member

      Moving the 1 to the first instance worked on some level, but only when I pull in a long date range, i.e., 2 years. What I'm trying to do though, is pull sales for any date range, say 1 week or 1 month and compare those sales to the previous year sales during the same time frame. I tried using this query

       

      sum(CASE
      WHEN year(`week_beginning`)-1 = year(`week_beginning`)
      THEN `sales`
      ELSE 0
      END)

       

      Which instead of using current_date() in the first instance, I use week beginning in hopes that whatever week I want to look at for this year, it will bring in previous year sales for the same week. This isn't working either, however.

    • Domo Employee

      Have you looked into the period over period graphs?

      1.png

       

    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