Year over Year Difference

Another question, wanting to calculate the difference of values over time.


I am counting new customers by year and wanting to know absolute and percentage growth. 

2015 : 100 customers

2016 : 125 customers (+25 customers, 25%)

2017 : 132 customers (+7 customers, 5.6%)

19replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • One way to do this would be to use the moving_sum function.


    if you have an attribute named yearno and a measure named numcust you could create 2 formulae as follows:

    CustGrowth - if ( moving_sum ( numcust , 1 , -1 , yearno ) = 0 ) then 0 else moving_sum ( numcust , 0, 0 , yearno ) - moving_sum ( numcust , 1 , -1 , yearno ) 

    CustGrowth% - if ( moving_sum ( numcust , 1 , -1 , yearno ) = 0 ) then 0 else (((safe_divide (moving_sum ( numcust , 0, 0 , yearno ), moving_sum ( numcust , 1 , -1 , yearno ) ) ) * 100 ) - 100 ) 

    Reply Like 1
  • Here's an example of what the output would look like, using a sample retail sales demo dataset that we have, I'm comparing revenue per year rather than number of customer.

    Reply Like
  • Hi Roland,

    You can also try doing “growth of count customer id by date yearly” to see the percentage growth.For more information hit the help button on top right corner and go to keywords to look for more such keywords.

    Reply Like
  • Paul Froggatt  Thank you Paul..  will let you know how it works soon.

    Reply Like
  • Paul Froggatt I did try the formulas you mentioned however i am getting errors when trying to save them.

    Mix of aggregated and non-aggregated arguments to the function[MovingSum] is not allowed.

    My "numcust" equivalent is a : sum(if) formula

    and my "yearno" is a the formula: year(date)


    Thank you for the continued support.

    Reply Like
  • Roland Schlichting 

    Since the Cust_Growth formula mentioned by Paul has a moving_sum in it, you can try removing the sum from the numcust formula. 


    So the numcust formula would simply have the if function only.

    Reply Like
  • Ok.  So now it calculated something, but I don't know what it's calculating.

    I split up the fiscals and none of the numbers make any sense on the calculations.  here a sample image of what the "before", "what I want to see" and "What I'm seeing"..

    Hope this helps explain the issue.

    Reply Like
  • Hi Roland,

    Perhaps it is best to schedule a 1:1 Office Hours session to provide further assistance to you on this topic, you can do this via this link

    Regards - Paul

    Reply Like
  • Your above "link for 1:1 Office Hours" is blocked by our corporate website due to it not meeting corporate policies and could potentially expose a risk.  Is there an email link instead?

    Reply Like
  • Aditya Abhinav Aditya, this shows percentage growth, i looked and didn't see a absolute number growth which is also important.  thanks though.

    Reply Like
  • I am also trying to do somethings similar, like absolute change in costs year over year, but it doesn't give me right answers. Is there a keyword i can use? or even by formula. 

    Reply Like
  • I tried using year-over-year, but it doesn't work with "yearly" bucket.

    Reply Like
  • Hi Divya - have you tried using VS?  What version of ThoughtSpot are you on?

    Reply Like
  • We are on currently, but are moving to 5 today . I am not aware of VS yet. will I be able to do this in version 5?

    Reply Like
  • Divya you are going to like VS.  I use it in 4.5 and is one of my staples.  I use it to compare Fiscals (e.g. Fiscal 2018 vs Fiscal 2019) or even things attributes like provinces (e.g. Ontario vs British Columbia vs All).  Happy Searching.

    Reply Like
  • Divya One other comment.  There's a good video that was produced by ThoughtSpot right here in the community which you should watch on VS and other Keywords.  I found it helpful.

    Reply Like 1
  • Divya in addition to the excellent in-depth video on "vs" that Roland Schlichting pointed you to, there is also a short 2-minute video here: (second row, "Use Versus for Comparison"). But the one on the community has multiple examples and use cases, so well worth the time.

    Reply Like
  • Thank you Victoria and Roland. These videos are very helpful. I tried vs in version 5 and it worked.

    Reply Like 1
  • Divya Awesome! Glad to hear it!

    Reply Like
Like1 Follow
  • Status Answered
  • 6 mths agoLast active
  • 19Replies
  • 1113Views
  • 9 Following