week to date change

I have a production date on many of my deposits. What I would like to do is to create a week to date change, a month to date change, and a year to date change giving me dollar amounts. What formula can be used to achieve this.

40replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Here are a number of formulas that should get you where you need to be on this one. Use the formulas in combination to get what you want. But do take up the offer of thoughtspot they can also teach you live how to do a lot of this stuff.


    Year to date whole month only i.e. we are in the middle of June but give me to Jan to the end of May

    sum_if ( year ( reporting date )=year(now()) and reporting date < start_of_month(now()), spend usd )

    Year to date

    sum_if ( year ( reporting date )=year(now()) and reporting date <= now(), spend usd )


    Last Year (whole Year)

    sum_if ( (year( reporting date) = year(now())-1 ), spend usd ) 

    Last year to date whole month to date i.e. if it is June 2018 now only give me Jan – May 2017

    sum_if ( (year( reporting date) = year(now())-1 ) and month_number( reporting date) < month_number(start_of_month(now())), spend usd ) 

    Current month to date (CMTD)

    sum_if (month_number(now()) = month_number( reporting date) and reporting date <= now() and year( reporting date) = year(now()), spend usd) 

    Last Month (LM)

    sum_if(reporting date < start_of_month(now()) and reporting date >= start_of_month(add_days(start_of_month(now()),-1)),spend usd) 


    so use them in combination e.g. LM-CMTD etc...


    Look into the rolling solution as well posted earlier as this is actually the easiest to implement once to get your head around what it does.

  • thank you! that worked. Now my last question is that I have a column called mtd avg balance amount and what i need to do is calculate todays value and subtract it from the same day as last month. For example i need to subtract 6/21/18's value- 5/21/18's value. Id appreciate the help on this as well. Thanks in advance

  • Craig Haughan it will be a sum of the balance btw. Thanks!

  • is there any way to store a date in a string using a formula and then use that string as a production date to subtract a balance amount

  • Hamsa,


    Take a look at the to_date function that convert strings to formulas.

  • its not a sum anymore its just the column. All i want to do is subtract todays prod date column-last month's same date prod date column Siva Singaram

    Like 1
  • Siva Singaram i just need to subtract the balance from one date from the same day just last month

  • sum_if(reporting date = now(),spend usd ) -(if(day (add_days(start_of_month(now()),-1)) > day (now())) then day(now()) else day (add_days(start_of_month(now()),-1)) = day( reporting date) and month_number( reporting date ) = month_number(add_days(start_of_month(now()),-1)) and year( reporting date) = year(now()),spend usd) 

    Like 1
  • Sorry ignore the above it didn't copy correctly this is the formula


    sum_if (now() = reporting date ,spend usd ) - sum_if ( if (day (add_days(start_of_month(now()),-1)) > day (now())) then day (now()) else day (add_days(start_of_month(now()),-1)) = day( reporting date) and month_number( reporting date ) = month_number(add_days(start_of_month(now()),-1)) and year( reporting date) = year(now()),spend usd)

    Like 1
  • Actually thinking about it. You will have to use 2 formulas because otherwise you will have to use moving_sum and calculate the -ve days and I can't write that without access to a thoughtspot instance to test it.


    sum_if (date(now()) = date(reporting date) ,spend usd ) 

    Same Day Month Ago (it will be the last of the month if there are more days in the month after)

    sum_if ( if (day (add_days(start_of_month(now()),-1)) > day (now())) then day (now()) else day (add_days(start_of_month(now()),-1)) = day( reporting date) and month_number( reporting date ) = month_number(add_days(start_of_month(now()),-1)) and year( reporting date) = year(now()),spend usd)



    Like 1
  • hello Craig Haughan I appreciate all of your help. However it is not a sum its just a field. whenever i sum them i get a 0.

  • I am also noticing that i need to calculate yesterday's production date compared to a month ago as we load in a day late

  • Siva Singaram Is there any way I can receive help on this on monday 8:00 AM eastern time because this is a time sensitive matter and Fridays do not work in my schedule

  • These are my formulas so far:

    today: sum_if (yesterday= date( production date ) , current balance amount ) 

    samedaya month ago:

    sum_if ( if (day (add_days(start_of_month(now()),-1)) > day (now())) then day (now()) else day (add_days(start_of_month(now()),-1)) = day( production date) and month_number( production date ) = month_number(add_days(start_of_month(now()),-1)) and year( production date) = year(now()), current balance amount ) 

    overall change:

    today -samedaymonthago 


    when i get an overall change it seems to subtract a large number from 0 for today and a large number minus zero on the same day a month ago. The reason being is it does not see that i want to subtract today - the number from last month. Please see if we can change this formula to work out.

  • All in all, I just need to use those formulas except without a sum_if because it is not a sum but rather just a singular value

Like2 Follow
  • Status Answered
  • 1 yr agoLast active
  • 40Replies
  • 1094Views
  • 4 Following