Change MTD Parameters Automatically

Is there a specific MTD parameter to put into our answers that will update at the beginning of each month?  Currently I have to go into each pin on my pinboard and manually edit February 2020 to March 2020.

7replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • You can try using the keyword "max" on the date[time] field in your search.

    Like 1
  • I haven't tested these but the following logic should work

    1. Write a formula to determine the Month to Date for comparison. 

    _MTDMonth = start_of_month(group_max(posting date)) - this returns me the start date for the current month.

    2. Write a dynamic formula to return total for period. 

    $ MTD = sum_if(start_of_year(posting date) = _mtdmonth, $ amount) - this sums all transactions for the current month. 

    Like
  • You could also use now() to create a month parameter.

    CurrentMonth=start_of_month(now())

    PreviousMonth=start_of_month(add_days(CurrentMonth,-1))

    MonthToDay=sum_if(start_of_month(posting_date)=CurrentMonth and posting_date < =date(now()), Amount)

    PrevMonthToDay=sum_if(start_of_month(posting_date)=PreviousMonth and day_of_month(posting_date) <= day_of_month(now), Amount)

    This can similarly be done to create a Year to Date or Prior year to date group/filter

    Like
  • You should really use today() rather than now().  

    today() is derived once per day and then cached, since it just returns the current date which is static for the entire day, whereas now() is derived every time it is called, since it returns the current timestamp which changes every time it is called, hence you should get better performance if you use today().

    Like 2
  • Hey, Is keyword "This Month" not working for you?  Otherwise, if you have to compare in a formula, using YourDate >= start_of_month(today()) and YourDate <= today() should work.  

    Like 1
  • Rachel Kozloski I thought about 'This Month' as well, but I have found that depending on the data set, there may be values in the date in 'this month' but to be processed in the future.  In the future, it may be advantageous to introduce a 'to date' keyword.  
    This could be something like this year to date.  to date keyword would mean a time frame up to the day of month of today.  This should then permit something like 'last year to date' and 'this year to date' adding date filters from the start of default/custom calendar to the day of month.  

    Like
  • Hey Curtis Balusek, PhD. ,

    "Month to date" , "Year to Date" , and "Week to date" are currently available keywords.  Sounds like they would help you out! :) 

    Thanks,

    Rachel

    https://docs.thoughtspot.com/5.2/reference/keywords.html

    Like 2
Like Follow
  • Status Answered
  • 2 mths agoLast active
  • 7Replies
  • 39Views
  • 6 Following