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
  • for example if I had deposits of $5000 last week and this week I only had $4500 I would like it to display -500 for week to date change. Also, which chart type would be best to display this information and can it be done in a normal chart.

    Like 1
  • @Siva Singaram I can only get percentage differences how can I adjust it to show dollar amount differences

    Like 1
  • Siva Singaram Adam Calabrese

  •  Hamza Alsalman

    Our current growth keyword provides only the capability to calculate percentages. The only that I can think of doing this is with the help of a couple of aggregated worksheet. One for current week's deposits and the other for last week's. You could then join them and in a new answer find the difference between them. Let me know if you want help in doing it this way?

    Like 1
  • I import in new data every day if we go about it in this manner will it update automatically?

  • Siva Singaram i import in new data every day so if we continue with your recommendation will this information be updated as the weeks go on.

  • Siva Singaram would i have to import data each week into 2 separate worksheets

  • You can create 3 formula's (you can do it in just one go but given you can't format formulas very easily it may be tough to understand) I think that sum_if is a pretty new addition but this can also be re-written as if...then...else if you have an older version of the software.

    Formula 1: This week

    sum_if(start_of_week (now()) <= reporting date and add_days(start_of_week(now()),7) >= reporting date, spend usd) 

    Formula 2 Last Week

    sum_if(start_of_week (add_days(now(),-7)) <= reporting date and add_days(start_of_week(add_days(now(),-7)),7) >= reporting date, spend usd )


    Formula 3: Weekly Change

    This Week - Last Week

    Replace "Reporting Date" with your date and "spend usd" with your USD value.

    You can then repeat with start_of_month and start_of_year.

    Hope this helps

  • Craig Haughan It seems to work for the comparison of the last two weeks yet it doesnt show the previous weeks and shows them as $0 change. 

  • Yes because the formula is only for this period v last period if you want to do it on a rolling basis it becomes more complicated and it would go something like:


    Formula: Change

    moving_sum( spend usd ,1,-1,month_number( reporting date)) - moving_sum(spend usd,-1,1,month_number(reporting date))


    Which would take last month from this month but .... this formula will only work for a single calendar year you have create an answer that is ordered by month_number as that is the way moving sums work. There is no week_number or quarter_number yet but I believe they are coming.

    In reality to solve you problem completely you will have to create a calendar_ref table to join on your reporting date with a set of ordinal sequence columns grouping together dates for your periods to use for your moving sum calculations but that is a little to complicated to explain in a forum post, it may be best to ask a data architect in your organisation to help (or maybe your TS contact can help with data design to solve your problem)

  • ok thank you anyways that information helped a lot

  • Craig Haughan said:
    sum_if(start_of_week (add_days(now(),-7)) <= reporting date and add_days(start_of_week(add_days(now(),-7)),7) >= reporting date, spend usd )

     would month be start of month and replace the 7s with 30's and for year would it be start of year and replace the 7's with 365?

  • I just noticed in my month to date section when comparing 6/1/18 to today 6/19/19 the difference in current balance showed lets say 200 Billion when in reality it should be about 1 Billion. What am I doing wrong here.

  • Yes but not quite because inconveniently months have different numbers of days. I have attached an excel file called cal_ref it is an example of how to create an ordinal sequence for a date in a dimension or reference table.

    Import the table to thoughtspot (or have your admin do it) you may have a table like this already so don't duplicate it if you have just use it instead.

    Calendar_Date_Id is the primary key and should be joined to your fact table on the reporting date that you choose either by creating a reference through the UI or better still through TQL.

    you can then use the formula:

    moving_sum( spend usd ,1,-1,Calendar_Week_Sequence) - moving_sum(spend usd,-1,1,Calendar_Week_Sequence)

    To do the weekly rolling change you have to add the column Calendar_Week_Sequence to the answer and order by it to make it work correctly. Use Calendar_Month_Sequence for month etc.

    You can create similar lookups for other date sequences e.g. Fiscal periods etc. but I will leave that for you.

    Hope it makes sense, as I said it is a little complicated for a forum post. Let me know if it makes sense.

  • thanks but could you explain this formula: sum_if(start_of_week (now()) <= reporting date and add_days(start_of_week(now()),7) >= reporting date, spend usd) i would like to understand what it is you are doing here because thoughtspot does not have in depth formula  syntax review

  • There are a number of keywords or functions here

    sum_if(criteria,value) if the criteria is true then add the value otherwise ignore it

    "and" logical expression where both sides have to evaluate to true for the result to be true

    now() todays datetime

    start_of_week calculates the date of the start of the week

    add_days: adds the number of days to the date (-ve will subtract them).

    so I am finding the start date of the week and the end date of the week by adding 7 days to the start and only adding the values up (spend USD) if the reporting date is between them.

    so put in English I am asking thoughtspot to:

    Add up Spend USD if reporting date is greater than or equal to start date of this week and reporting date is less than or equal to end of this week.

  • that may be the issue. I want to subtract USD every day so if i have 40 million today and yesterday I had 35 million i should see 5 million. -ve is not working for me

  • Yes, you have to calculate the period before as well. This is only half of the formula you need

  • thank you. so i have went about the week to date change, but when I try to change the formula to fit the month to date and year to date change I seem to be getting very large numbers

  • is there any way you could write out the month to date and year to date formulas

  • Happy to write them, but just to clarify in the year to date do you want the whole prior year Jan to Dec even if it is June or so you would be comparing 12 months last year with 6 months this year i.e. Calendar year to date? or do you want a rolling 12 months i.e. one whole year compared with the other whole year ignoring the calendar? Also do you want the year to be defined in whole months or days?

  • year to date comparing jan 1 2018 to now and month to date would be the first of the month compared to my production date

  • years could be defined in days or months i suppose

  • Hi Hamsa,


    Sorry for the delay. It looks like you need quite a few things here and it would be better for us to understand the requirements better before jumping to a solution.

    It would be best if you could get on a call with us. You can schedule this call through https://thoughtspotcs-officehours.youcanbook.me/


    Thanks for your patience,


  • thank you but unfortunately those friday times do not work for me, however Craig Haughan has been a great help thus far and we are close to finding a solution. 

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