Help

Conditional Roll Up within a Table

Topic Labels: Formulas
2506 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Brett_Snelgrove
6 - Interface Innovator
6 - Interface Innovator

Hi there,

I would love to get some guidance on how to do something like a conditional roll up within one table.

If you see the table example below you can see that I have contractual start and end dates that I want to use as a guide for calculating Total Revenue so that I can calculate % of monthly revenue against the total revenue.

36

It is the TOTAL REVENUE column that I am having difficulty with as I don’t know how to make the calculation conditionally based on the contractual year that a particular month falls within. And as you can see, a contractual year may differ from calendar to financial year.

Is there a way to do this?

Thanks in advance!

1 Reply 1

I would use a linked table to assist with the calculations. Make a table called [Contractual Years]. The primary field would be a formula to concatenate two other fields where you specify the start and end month-year combo for a given contractual year:

33%20AM

In your primary table, you would link to a specific contractual year for each month. On the [Contractual Years] table, you would add a rollup field that would tally all of the monthly revenue values for linked months.

11%20AM

Back in your main table, you would add a lookup field to pull in this total.

31%20AM

A {Percent of Total} formula field would do the math to determine how much each month factored into the total.

IF({Total for Contractual Period}, {Monthly Revenue} / {Total for Contractual Period})

57%20AM