Rolling Total in Table View

Hello,

I'm sure this is easy but I can't seem to get this to work in the table view. Here is my data and my desired outcome is the "TY MTD Actuals".

Day	Day(Name)	Total	TY MTD Actuals
GRAND TOTAL		$118,273
2020-01-01	Wednesday	$14,333	$14,333
2020-01-02	Thursday	$21,187	$35,520
2020-01-03	Friday	$22,732	$58,252
2020-01-04	Saturday	$22,863	$81,115
2020-01-05	Sunday	$24,929	$106,045
2020-01-06	Monday	$12,229	$118,273

Find more posts tagged with

Accepted answers

GrantSmith

Hi @DMJerry ,

What you're looking for is a windowing function in a beast mode. This isn't enabled by default so you may need to talk to your CSM to get it enabled in your instance but you can get a running total utilizing a window function like:

SUM(SUM(`Total`)) OVER (ORDER BY `Day`)

You can also utilize a window function to get the grand total but it'll return the same value for each row in a new column:

SUM(SUM(`Total`)) OVER ()

Instead you'd want to utilize the Include Total Row option on the card if you only want a single row with the grand total.

All comments

GrantSmith

Hi @DMJerry ,

SUM(SUM(`Total`)) OVER (ORDER BY `Day`)

You can also utilize a window function to get the grand total but it'll return the same value for each row in a new column:

SUM(SUM(`Total`)) OVER ()

Instead you'd want to utilize the Include Total Row option on the card if you only want a single row with the grand total.

DMJerry

Awesome! I'll ask my CSM and try this. Appreciate it.