Comparing days of data 364 days apart

I can't believe this isn't as simple as I think it should be. The dataset has transactions which have a corresponding date value. I need to create a column to compare values for dates 364 days apart. Seems like it should be so simple.

This beast mode gets me the current day units:

IFNULL(`GG_ELIG_UNITS`,0)

(the column set up with a SUM aggregation)

These beast modes all validate but return unwanted values as noted after the -----:

SUM(IFNULL(CASE WHEN `DATE_VALUE`-364 THEN `GG_CONV_UNITS`ELSE 0 END,0)) ----- which returns all zero's

SUM(CASE WHEN (`DATE_VALUE`- 364) THEN `GG_CONV_UNITS` END) ----- which returns all zero's

SUM(CASE WHEN (`DATE_VALUE`-364) =`DATE_VALUE` THEN `GG_CONV_UNITS` END) -------which returns all blank cells

and I've tried lots of variations on this! Tried without the SUM in the beast mode and set it up on the column after added.

I 'm using 364 days because we use a fiscal calendar and I want to compare the same day fiscal year this year to fiscal year last year.

This beast mode always returns the correct corresponding day in the previous fiscal year:

`DATE_VALUE`-364

I just can't get to the point where I'm collecting multiple rows of data from a day 364 days ago and summing them for the previous year.

Seems like it should be really simple!

Find more posts tagged with

Comments

GrantSmith

Hi @user016095

This is a common question regarding looking back in time. The best way to think about this is to restructure your data. With your beast mode you're asking essentially is 1 = 1 - 364 which will never be true because beast modes compare data within the same row.

What you can do is join your data to itself but have the date offset os that each date your reporting on has relative data associated with it.

I've outline this method in this post here:

https://dojo.domo.com/t5/Card-Building/show-percent-on-period-over-period-graph/m-p/50540/highlight/true#M7313

It allows you to have your own custom period comparison types (in your case 364 days) and you simple end up with a report date and a comparison date (for example you're graphing 1/1/2021 in your report but you might have 1/1/2020 for last year, 1/2/2020 for least year (day of week aligned) 12/25/2020 for last week etc) You can then utilize some beast modes to calculate the total for last year, this current year etc.