How create a incremental sum based on date

[DaGi07]

Hello guys,

i'm trying to create a incremental sum divided for different number for each row that are originate by the difference between two date in two different tables--> BUDGET_ODOO[Fine_progetto] and BUDGET_VS_CONS[Periodo]  

I try to explain you better with these screen that show the structure of table BUDGET_VS_CONS

How can i convert this spreadsheet formula in a appsheet expression? 

second question: 

"remaining months at the end of the project" should be the difference between filed BUDGET_ODOO[Fine_progetto] and BUDGET_VS_CONS[Periodo] of each row (in order to decrease the result number as the [Periodo] increases

I tried with this expression but it doesn't works because [Fine_progetto] is in another table

((((YEAR([Fine_progetto])
- YEAR([Periodo])
)* 12)

+ MONTH([Fine_progetto]))
- MONTH([Periodo])))

I hope you will help me! tks

[Marc_Dillon]

I can't understand what you're wanting to accomplish. Can you explain it another way?

[DaGi07]

@Marc_Dillon of course, i'll hope to be clearer with this detailed sheet:

tks 

[dbaum]

I think you may be trying to come up with something like the following expression for the App formula property of the Residual_budget column.

[Progetto].[Budget] - SUM(SELECT(BUDGET_VS_COND[Costo], AND([Progetto] = [_THISROW].[Progetto], [Periodo] <= [_THISROW].[Periodo])))

[DaGi07]

@dbaum tks! the expression works! but i have to divide the result for the number of month that result from the difference between the filed [fine_progetto] and [periodo] (column M in gsheet)

(
(
(
(
YEAR([Fine_progetto])
- YEAR([Periodo])
)
* 12
)
+ MONTH([Fine_progetto])
)
- MONTH([Periodo])
)

this espression works in table BUDGET_ODOO but not in BUDGET_VS_CONS because it not find the filed [Fine_progetto]

[dbaum · Accepted solution]

Just like in the expression I drafted for you, if you have an expression in Table  1 that needs to reference a column in Table 2 and you've established references between the tables, you can use the "dot notation" convention of citing the column from Table 1 that serves as the link to Table 2 followed by "." followed by the column from Table 2. So, wherever you need to use [Fine_progetto] from the BUDGET_VS_CONS table, try instead [Progetto].[Fine_progetto]. 

[DaGi07]

@dbaum in table BUDGET_VS_CONS i created the filed Fine_progetto = ANY(SELECT(BUDGET_ODOO[Fine_progetto],[Progetto] = [_THISROW].[Progetto]))

and then i used this expression to get the remaining months to the end of project

(SUM(
SELECT(BUDGET_ODOO[budget],[Progetto]=[_THISROW].[Progetto])) - SUM(SELECT(BUDGET_VS_CONs[Costo], AND([Progetto] = [_THISROW].[Progetto], [Periodo] <= [_THISROW].[Periodo]))))

/

(
(
(
(
YEAR([Fine_progetto])
- YEAR([Periodo])
)
* 12
)
+ MONTH([Fine_progetto])
)
- MONTH([Periodo])
)