Compare against a specific date?

mberkeley
mberkeley 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:

Best Answer

  • david_cunningham
    david_cunningham 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

    AVG(case when most_recent_flag = 1 then value end)

    And the second part to calculate the comparison value

    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

  • david_cunningham
    david_cunningham 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.

    (AVG(case when most_recent_flag = 1 then value end)
AVG(case when date = STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE(),'0101')),'%Y%m%d') then value end))
    
/
    
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! ✔️**

  • ArborRose
    ArborRose 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. 

    Current Year (YTD):
    

sum(
    
case when YEAR(mydate) = YEAR(CURRENT_DATE())
    
and mydate <= CURRENT_DATE()
    
then amount else 0 end
    
)
    

Previous Year (YTD):

    
sum(
    
case when YEAR(mydate) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
    
and mydate <= DATE_ADD(CURRENT_DATE(),-365)
    
then amount else 0 end
    
)
    

Difference between CY and PY:

    
sum(
    
case when YEAR(mydate) = YEAR(CURRENT_DATE())
    
and mydate <= CURRENT_DATE()
    
then amount else 0 end
    
)-
    
sum(
    
case when YEAR(mydate) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
    
and mydate <= DATE_ADD(CURRENT_DATE(),-365)
    
then amount else 0 end
    
)

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

  • mberkeley
    mberkeley 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
  • david_cunningham
    david_cunningham 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

    AVG(case when most_recent_flag = 1 then value end)

    And the second part to calculate the comparison value

    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! ✔️**

  • mberkeley
    mberkeley Contributor

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

Categories