Sum previous month traffic

user003049
user003049 Member
in Archive

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?

Comments

  • rado98
    rado98 Contributor

    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
    user003049 Member

    Thanks for reviewing my code.

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

    Name,Last Month Traffic,Latest Month Traffic

  • Unknown
    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
    user003049 Member

    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
    user003049 Member

    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
    rado98 Contributor

    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
    user003049 Member

    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.

Categories