YOY & Net diff Beatmode calc
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:
- What would be the appropriate beast mode to be able to capture the comparison to the the same 15 day period LY.
- 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?