YOY & Net diff Beatmode calc

Golfoholic

I have kind of a unique YOY period that I am trying to create a report around. I am taking counts of leads that come through different marketing 'Marketing Sources', and only looking at the past 15 day window for each. This is straight forward. Take the counts for each source in the `Marketing Source` columns and use the date filter to only look back in the past 15 days. Like so...

Now where it is getting tricky is I want to find the net diff, and % diff compared to the same 15 day period from the prior year. The beastmode I tried to find the diff is:

(sum(case when YEAR(`date_entered`) = YEAR(Date_Sub(CURRENT_DATE(), interval 1 year)) and MONTH(`date_entered`) = month(CURRENT_DATE()) then 1 else 0 end)

-

sum(case when YEAR(`date_entered`) = YEAR(CURRENT_DATE()) and MONTH(`date_entered`) = month(CURRENT_DATE()) then 1 else 0 end))

But it isn't resulting how I would hope. My biggest questions are:

1. What would be the appropriate beast mode to be able to capture the comparison to the the same 15 day period LY.
2. Can this be done and have it interact with the date filter, say I change the date filter to the last 30 days instead of 15, would this net diff then reflect the difference from the prior 30 days a year ago?

MarkSnodgrass

When you use the date filter to only show the last 15 days, you are eliminating your data from the prior year, which is why your beast mode is not working as you would expect. You need to change your date filter to the last 12 months, at least.

You can then create a beast mode to filter to the 15 day window you are looking for by using something like this:

(CASE when DAYOFYEAR(`dt`) <= DAYOFYEAR(CURRENT_DATE()) and DAYOFYEAR(`dt`) >= DAYOFYEAR(CURRENT_DATE())-15 THEN 'Last 15 Days'
ELSE 'Other'
END)

Put this beast mode in your filter and then select 'Last 15 Days'. This should get you what you need.

Golfoholic

@MarkSnodgrass Looks to be working much better. Only thing I did different was on the date filter instead of last 12 months, I used last 720 days just to have an overly wide range of data pulled in then filtered to 'Last 15 Days'

My last question is my YOY % beast mode. There seems to be a lot of unexpected variance. What I am using is:

(sum(case when YEAR(`date_entered`) = YEAR(Date_Sub(CURRENT_DATE(), interval 1 year)) and MONTH(`date_entered`) = month(CURRENT_DATE()) then 1 else 0 end)

-

sum(case when YEAR(`date_entered`) = YEAR(CURRENT_DATE()) and MONTH(`date_entered`) = month(CURRENT_DATE()) then 1 else 0 end))

/

sum(case when YEAR(`date_entered`) = YEAR(Date_Sub(CURRENT_DATE(), interval 1 year)) and MONTH(`date_entered`) = month(CURRENT_DATE()) then 1 else 0 end)

MarkSnodgrass

I would separate out the two calculations (this year, last year) and put include them in the card and see what those individual numbers look like. That will help identify where the problem is. I would start there.