YoY change beastmode at card level in table chart

pauljames

Hi,

How would I create a beastmode at the card level that would specifically take a year vs. year comparison for each column of data (IL expl dollars, il expl qty, exp tp cost, etc.)

Currently selecting transpose on this card. I'd like an additional column that calculates 2019 vs. 2020 change %.

Thanks!

Paul

DashboardDude

@pauljames

Here you go: https://www.loom.com/share/a3c036d95af54206a9c52dcfdc6876cf?sid=4d72f44b-1afb-47dc-8e7b-7e805de8feda

ArborRose

You can create formulas for each year. That way its dynamic and you don't have to hard code the years. For year to date use the following. Take out the condition that says and date less than equal current date if you want the full year. If your data field is amount, simply use [amount] and add the topics and it will break on the topics.

Current Year:

sum(
case when YEAR(date) = YEAR(CURRENT_DATE())
and date <= CURRENT_DATE()
then {yourdatafield} else 0 end
)

Previous Year:

sum(
case when YEAR(date) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
and date <= DATE_ADD(CURRENT_DATE(),-365)
then {yourdatafield} else 0 end
)

Difference:
sum(
case when YEAR(date) = YEAR(CURRENT_DATE())
then {yourdatafield} else 0 end
)-
sum(
case when YEAR(date) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
then {yourdatafield} else 0 end
)

MarkSnodgrass

You may want to try how I outline to do this in this video:

https://youtu.be/mDwSQGAjIrs

pauljames

@MarkSnodgrass, it was informative! But, it's not really what I'm looking for here.

DashboardDude

@pauljames

Here you go: https://www.loom.com/share/a3c036d95af54206a9c52dcfdc6876cf?sid=4d72f44b-1afb-47dc-8e7b-7e805de8feda

ArborRose

You can create formulas for each year. That way its dynamic and you don't have to hard code the years. For year to date use the following. Take out the condition that says and date less than equal current date if you want the full year. If your data field is amount, simply use [amount] and add the topics and it will break on the topics.

Current Year:

sum(
case when YEAR(date) = YEAR(CURRENT_DATE())
and date <= CURRENT_DATE()
then {yourdatafield} else 0 end
)

Previous Year:

sum(
case when YEAR(date) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
and date <= DATE_ADD(CURRENT_DATE(),-365)
then {yourdatafield} else 0 end
)

Difference:
sum(
case when YEAR(date) = YEAR(CURRENT_DATE())
then {yourdatafield} else 0 end
)-
sum(
case when YEAR(date) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
then {yourdatafield} else 0 end
)