Sum previous month traffic

I need to sum the latest month's and the previous month's traffic for each Name. I'm trying with beast mode.
My data is like so:

Name Date Traffic (desc)
AAAA 2018/10/31 2,358,817
AAAA 2018/11/06 2,483,452
BBBB 2018/10/30 2,555,555
BBBB 2018/11/06 2,123,123

As the 'day' part varies from month to month I tried cutting off the day with DATE_FORMAT() for latest month.

case when DATE_FORMAT(`Date`, '%Y-%m') = max(DATE_FORMAT(`Date`, '%Y-%m')) then `Traffic (desc)` end

But I cannot get the calculation for the previous month to work. I get a blank with the following.

case when DATE_FORMAT(`Date`, '%Y-%m') = DATE_SUB(max(DATE_FORMAT(`Date`, '%Y-%m')), interval 1 month) then `Traffic (desc)` end

I also get a blank with MONTH().

case when MONTH(`Date`) = DATE_SUB(MONTH(MAX(`Date`)), interval 1 month) then `Traffic (desc)` end

I'm not sure what is wrong here.

Can somebody help me?

Find more posts tagged with

Comments

rado98

I cna see an issue with the beastmodes:

MAX doesnt work for case statements, at least not in the way everyone would want.

The MAX value you woudl be getting is the MAX of the row, which is just tha value in the row.

What sort of output card do you need? Table or graphs.

user003049

Thanks for reviewing my code.

I'm aiming for a table with columns like this.

Name,Last Month Traffic,Latest Month Traffic

unknown

Hi, user003049,

I think you can accomplish this in a Beast Mode calculation

There are probably several different ways you could do it, and the best option would depend on how you're trying to visualize it. In your case, since you're looking for a table output, I recommend you use two Beast Mode calculations. The first will include the traffic number of a row if the date of the row is in the current month (and will be NULL for all other rows). The second will include the traffic number of a row if the date of the row is in the prior month (and will be NULL for all other rows).

You can create these Beast Mode calculations using the MONTH(), CURRENT_DATE(), and DATE_ADD() functions.

Your current month calculation is pretty easy:

case when MONTH([date field]) = MONTH(CURRENT_DATE()) then [traffic field] end

The previous month calculation is only a little more complex. You'll need to use the DATE_ADD function:

case when MONTH(DATE_ADD([date field], interval 1 month)) = MONTH(CURRENT_DATE()) then [traffic field] end

Hope that helps!

user003049

Thanks DanB,

I could get the correct data by adding a sum distinct to your answer.

SUM(DISTINCT (case when MONTH(DATE_ADD(`Date`, interval 1 month)) = MONTH(CURDATE()) then `Traffic (desc)` end))

Unfortunately this is not the best answer for me as the data is uploaded around the second week of every month, so I might get a blank or error for the first few weeks every month?

I wish there was a way to get the previous month with max date instead.

user003049

So I transformed the date column to "YYYY-mm-01" so I don't need to cut off the day.

I might be able to get correct data with this using CURDATE. It's lengthy but it seems to work.

case when DATE_FORMAT(CURDATE(), '%Y-%m-01') = max(`Date`)
then (SUM(DISTINCT(case when DATE_FORMAT(SUBDATE(CURDATE(), interval 1 month), '%Y-%m-01') = `Date` then `Traffic (desc)` end)))
else (SUM(DISTINCT(case when DATE_FORMAT(SUBDATE(CURDATE(), interval 2 month), '%Y-%m-01') = `Date` then `Traffic (desc)` end)))
end

rado98

Hi, I think using Max(Date) might still be a problem.

I can think of a couple of way to do it. The easier one to explain would be:

Create a column with the Max Date in ETL, then use that to compare using a Beast mode

Alternatively, although more complicated and I would not be able to give you the code without try and error.

Sum traffic if (sum of this month's traffic is greater than 0), else sum of last month;s traffic.

Same principle for last month's traffic.

user003049

Thanks rado98, that sounds much simpler. I'll try that if I still get an error as I get more data. I only have two months worth at the moment.