# Year over Year Difference

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

Example:

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
• Popular
• One way to do this would be to use the moving_sum function.

e.g.

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 )

• 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.

• 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.

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

• 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.

• 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.

• 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.

• 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 https://thoughtspotcs-officehours.youcanbook.me/

Regards - Paul

• 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?

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

• 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.

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

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

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

• 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.

• 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.

• 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: https://docs.thoughtspot.com/5.0/release/videos-list.html (second row, "Use Versus for Comparison"). But the one on the community has multiple examples and use cases, so well worth the time.

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