How to have a cumulative sum split by user id over time?

I load my relevant columns into thoughtspot as below:

  Weekly Date   User ID   Profit
 ------------- --------- --------
  01/01/2020        123       23
  08/01/2020        123       35
  15/01/2020        123       12
  01/01/2020        918       53
  08/01/2020        918      279
  15/01/2020        918      726  

 

I would like to calculate weekly profit growth per customer per week, and would like to start by generating a weekly cumulative sum of profit per user id.

 

I believe thoughtspot only has a cumulative sum function, which cannot group by any field. So I would like to kindly ask if anybody can support me in writing a formula to calculate this grouped cumulative sum.

 

Essentially, I would like the following

  Weekly Date   User ID   Profit   Cumulative Profit
 ------------- --------- -------- -------------------
  01/01/2020        123       23                  23
  08/01/2020        123       35                  58
  15/01/2020        123       12                  70
  01/01/2020        918       53                  53
  08/01/2020        918      279                 332
  15/01/2020        918      726                1058  

 

Thanks for your support in advance.

4replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • I have been doing this today actually! 

     

    Do cumulative_sum( profit, user ID) 

    Like
  • Marc Price Thank you for answering, but when I use this formula, it simply gives the original profit amount , rather than cumulatively adding it per week for every customer.

     

    I get the below:

      Weekly Date   User ID   Profit   Cumulative Profit
     ------------- --------- -------- -------------------
      01/01/2020        123       23                  23
      08/01/2020        123       35                  35
      15/01/2020        123       12                  12
      01/01/2020        918       53                  53
      08/01/2020        918      279                 279
      15/01/2020        918      726                726  
    Like
  • Try adding the date 

     

    cumulative_sum( profit, <date_column>)

    Like
  • Marc Price Thanks for that, seems to be working fine!

    Like
Like Follow
  • Status Answered
  • 1 mth agoLast active
  • 4Replies
  • 15Views
  • 1 Following