Compare against a specific date?

mberkeley

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?

david_cunningham

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

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.

david_cunningham

@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.

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

Happy to answer any questions you may have 😁

ArborRose

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

)

mberkeley

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

david_cunningham

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

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.

mberkeley

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