Question

Running total that subtracts subsequent values across rows

  • 28 September 2016
  • 4 replies
  • 1315 views

Hello,



I saw plenty of threads regarding creating running totals across rows, however is there a way to take the initial value in the first column, and then as the columns continue on, subtract the next values from the original.



Think of having a starting amount of $1,000. Then as the years progress, you subtract x amount from that initial $1,000. So year one may be 1,000 - 50, then year two: 950 - 25, then year three: 925 - 75… and so on. And this is happening across columns, not down a single column.



Thanks!


4 replies

Userlevel 4

Hi James,



You can probably just use a slightly modified version of the running total hack from here: Creating a running total across rows with table calculations



pivot_index(${aircraft.count},1)*2  ## you need the first column twice because 

- ## the running_total immediately subtracts it

sum(

pivot_offset_list(

${aircraft.count}

, 1 - pivot_column()

, pivot_column()

)

)



And here’s the example in learn:


https://learn.looker.com/explore/faa/aircraft?qid=Hgly55YH1BJsQhOmEY004G

Are the first line and what you have below the dash supposed to be two separate table calculations? How is that code able to be a table calculation

Userlevel 4

You just need to remove the comments to run this, the dash is just a minus and done to space the expression a little better. The example in learn shows the expression running live. Here’s a reformatted version without all the spacing:



pivot_index(${aircraft.count},1)*2-sum(pivot_offset_list(${aircraft.count},1-pivot_column(),pivot_column()))

This makes sense, Thank you!

Reply