Skip to content

    • Charting

    Charting

    Compare against a specific date?

    Contributor
    in Charting

    I have a dataset that aggregates data on a daily basis. I would like to compare the most recent value to the value on a specific date.

    Example:

    Date Value

    1/1/24 400

    1/2/24 403

    1/3/24 410

    ….

    5/7/24 500

    In my Multi Value card, I am using MAX of Date and Value to pull the value 500, then for comparison, I have the all the date options (days, Quarters, Years, etc…)

    I want to be able to get a % change between 5/7/24 and 1/1/24 (IE % change year to date).

    Any ideas?

    Tagged:

    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

    • Coach
      Answer ✓

      @mberkeley you can use the "Multi-value columns" comparative card.

      image.png

      Then use the first part of the previous beast mode to calculate the current value

      1. AVG(case when most_recent_flag = 1 then value end)

      And the second part to calculate the comparison value

      1. AVG(case when date = STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE(),'0101')),'%Y%m%d') then value end)

      Then set it up like this.

      image.png

      David Cunningham

      ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
      ** Did this solve your problem? Accept it as a solution! ✔️**

    Answers

    • Coach

      @mberkeley my suggested approach would be…

      Step 1: In your ETL, use the rank and window tile to assign the most recent data a value of 1. This will be used to filter in a beast mode. Say we call this MOST_RECENT_FLAG

      Step 2: Create your beast mode. You can set your comparison date by adjusting the values in the STR_TO_DATE function.

      1. (AVG(case when most_recent_flag = 1 then value end)
      2. AVG(case when date = STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE(),'0101')),'%Y%m%d') then value end))
      3. /
      4. AVG(case when date = STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE(),'0101')),'%Y%m%d') then value end)

      You can see that this allows us to calculate a YTD change with the most recent value. In this example dataset I used, the last date with values is March 31st.

      image.png

      If this answers your question, please 'like/heart' the response and then 'accept' as the answer.

      Happy to answer any questions you may have 😁

      David Cunningham

      ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
      ** Did this solve your problem? Accept it as a solution! ✔️**

    • Coach

      I have given this example in previous answers. Say you create a formula for the current year. You can use the same formula to also find the value for the year before {previous year}. To find the difference, just subtract one from the other.

      This should be the same thing you are working with. The difference being, you want to compare to a static date. 

      1. Current Year (YTD):
      2.  
      3. sum(
      4. case when YEAR(mydate) = YEAR(CURRENT_DATE())
      5. and mydate <= CURRENT_DATE()
      6. then amount else 0 end
      7. )
      8.  
      9. Previous Year (YTD):

      10. sum(
      11. case when YEAR(mydate) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
      12. and mydate <= DATE_ADD(CURRENT_DATE(),-365)
      13. then amount else 0 end
      14. )
      15.  
      16. Difference between CY and PY:

      17. sum(
      18. case when YEAR(mydate) = YEAR(CURRENT_DATE())
      19. and mydate <= CURRENT_DATE()
      20. then amount else 0 end
      21. )-
      22. sum(
      23. case when YEAR(mydate) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
      24. and mydate <= DATE_ADD(CURRENT_DATE(),-365)
      25. then amount else 0 end
      26. )

      ** Was this post helpful? Click Agree or Like below. **
      ** Did this solve your problem? Accept it as a solution! **

    • Contributor

      @david_cunningham Thanks. I am able to get the values for the dates and do the math as you suggested. What I can't figure out is how to use the multivalue card to show the Current Value and then the % change. like this:

      Snag_f6f0230.png
    • Coach
      Answer ✓

      @mberkeley you can use the "Multi-value columns" comparative card.

      image.png

      Then use the first part of the previous beast mode to calculate the current value

      1. AVG(case when most_recent_flag = 1 then value end)

      And the second part to calculate the comparison value

      1. AVG(case when date = STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE(),'0101')),'%Y%m%d') then value end)

      Then set it up like this.

      image.png

      David Cunningham

      ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
      ** Did this solve your problem? Accept it as a solution! ✔️**

    • Contributor

      @david_cunningham That did it. It would help if I used the right chart type 😁

    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