Month to date growth

Is there a way to get growth for the month to the current date? For example to compare August 1st to August 21st for 2016 to 2017? I have several people wanting to check what their growth is so far this month compared to the same time period the previous year.

14replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Thanks for your question, Steven. Unfortunately, there is no good way to achieve this in ThoughtSpot today. However, we have identified these types of “period ago comparison” use cases as a high priority. Our Engineering team is actively working on it.

  • Shailesh Chauhan - Can this not be accomplished with some for of if(...) statement? or Moving_Sum?


    Steven Bohlson - not sure if this helps, but we had to look at sales for the last 30 days, defining our limits for last 30 days. Here's the formula we are using: 

    if(add_days (now(), -2) >= date and date >= ( add_days ( now ( ) , - 32 ) ) ) then sales else 0 

    If you need to get the previous month sales, you could doing something like moving_sum(sales, 1, -1, date). moving_sum(sales, 13, -13) gives me last month last year. 

    Let me know what you end up going with as I'm currently looking at a similar business case. My trouble with the moving_sum is getting a month attribute/bucket to work if day level is displayed on the grid. (i.e current date = 8/28 displaying on table, but I want to see last's month sum sales 7/1-7/30, etc.).

  • Steph Fisher I'll have to try something like that out. It was actually your question about moving_sum that got me to ask my question.

  • Steven Bohlson

    You can calculate the Last Year MTD with a formula like this 

    if(month_number (date field)=month_number (today ()) and year(date field) = year ( today())-1 and day(date field)<day(today ())) then sales amount else 0 

    and you can calculate Current MTD with a formula like this ( assuming you don't have any sales for today or the future yet in the table)

    if(start_of_month (date field ) = start_of_month (today())) then sales amount else 0 

    Then you can do another formula to calculate the growth as 

    (sum(Current MTD) - sum(Last Year MTD))/(sum(Last Year MTD))

    Let me know if this is what you are looking for. As Shailesh pointed out, we are are in the process of creating much simpler ways of calculating formulas like this.

    Like 1
  • Siva Singaram That looks great. I'm going to verify the data but it looks just right at first glance.

  • Siva Singaram - This was very helpful. My calculation for growth was Last Month/Last Month Last Year-1. I had to capture sales for the entire month and had to account for if the month number fell in January. 

    Last Month Calc

    if(month_number (date )=( if ( month_number ( today()) - 1 = 0 ) then 13 else month_number ( today() ) - 1 ) and year(date ) = if ( month_number ( today() ) = 1 ) then year ( today() ) - 1 else year ( today() ) ) then sales else 0

    Last Month Last Year Calc

    if(month_number (date )=( if ( month_number ( today()) - 1 = 0 ) then 13 else month_number (today() ) - 1 ) and year(date ) = if(month_number(today())= 1) then year ( today() ) - 2 else (year (today ( ) )-1) ) then sales else 0

  • Steph Fisher , I think both of the 13s will have to be replaced with 12s. Otherwise this is perfect.

  • Siva  - I started with 12's but kept getting Novembers. The 13 puts me at December with month_number(today())-1 portion of the formula. 

  • Steph Fisher , the left hand side is month_number (date )and hence the right hand side should return 12 and not 13.

  • Siva - Figured it out - I needed better placement of parentheses:

    if(month_number (date )=( if ( month_number ( today()) - 1 = 0 ) then 12 else (month_number ( today() ) - 1 ) and year(date ) = if ( month_number ( today() ) = 1 ) then year ( today() ) - 1 else year ( today() ) ) then sales else 0 . 

    I think it was working with the value 13 because it was doing the math -1 after the fact. Regardless, formula now works with 12. Thanks!

  • Steph Fisher good. Just realized the function for start_of_month. So the Last Month calculation could also be done as

    if(start_of_month (date ) = start_of_month (add_days (start_of_month (today ()),-1))) then sales else 0 

    and for last month from last year it could be done as 

    if(start_of_month (date ) = start_of_month (add_days (start_of_month (today ()),-367))) then sales else 0

    The reason I am subtracting 367 instead of 366, is to take leap year into account.

  • Thanks Siva Singaram - I've been avoiding start_of_month due to the function returning Jan 2017 vs 01/01/2017 as cited in the documentation (running version 4.3). If I have some time will look into it. 

  • The bug with start_of_month is only a display problem. All calculations will work properly with it, like in this case. Anyways, your formula is fine, if you are more comfortable with it.

  • Steph Fisher wow!  That's some formula.

    Like 1
Like1 Follow
  • Status Answered
  • 2 yrs agoLast active
  • 14Replies
  • 1443Views
  • 6 Following