Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Running total that subtracts subsequent values across rows

  • 28 September 2016
  • 4 replies
  • 1315 views
jimRL

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

colin
Rank
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

jimRL

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

colin
Rank
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()))
jimRL

This makes sense, Thank you!

Reply


Terms of Service