Formula to get last year value

I want to compare sales current year vs budget current year vs sales last year (monthly basis).

Formula to calculate Sales Previous Year / Sales Last Year = moving_sum ( sales amount , 12 , -12 , Periode) 

How to display visualization like this? I just want to display current year only

4replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi,

     

    I am not in front of my machine so can't test the actual formula for last year. However rather than moving_sum I would use the approach in this thread. This thread is This Month vs Last Month but the same theory should apply.

    https://community.thoughtspot.com/t/y7f8xr

    Like 1
  • Hi Damian,

    Please check my current search in TS.

    My objective is to compare sales previous year vs Sales Current Year vs Sales Budget This year for 2020.

    Can we do it in ThoughtSpot?

    Like
  • I believe the following should work. Assuming that the Last Year formula is: 

    moving_sum(sales,12, -12, date ) .

    The key is we need to force ThoughtSpot to ignore the 'This Year' filter. This can be achieved with the group_aggregate function. So we can write the Last Year formula as: 

    group_aggregate (moving_sum(sales,12, -12, date ) , query_groups ( ) , {} ) 

    The problem will be if you apply an additional filter. I.e. Region = South. This filter will be ignored. You could apply region as a dimension in the search as this is handled as part of the second parameter query_groups()

    Like 1
  • Hi Damian Waldron ,

    Thanks for your help, my formula is basically same with you.

    I already found the solution using view, I put sales, budget, and sales previous year in the view and display chart from it.

    Like
Like Follow
  • Status Answered
  • 2 mths agoLast active
  • 4Replies
  • 99Views
  • 3 Following