How to select from 2 rows in a table

Status
Not open for further replies.

JustMe24

Technical User
I am working on a 13 month financial report (to use for budgeting). Actual numbers are fine, but I struggling to pull in the budget numbers.
My budget tables looks similar to this :
Account # Budget yr Period 1 Period 2 Period 3 (continues for 12)
606060 2024 50 50 50
606060 2023 45 45 45

So if I am running the report in say October 2024, I want budget numbers for Period 1-10 in 2024 but I also want periods 10-12 in 2023 (13 months)

I have a similar report that only uses current year budget numbers and my Year To Date formula is (more to it but you get the idea):

else If ({@Period}) = 10 then ({Command.Period_1_Budget}+{Command.Period_2_Budget}+{Command.Period_3_Budget}+{Command.Period_4_Budget}+{Command.Period_5_Budget}+{Command.Period_6_Budget}+{Command.Period_7_Budget}+{Command.Period_8_Budget}+{Command.Period_9_Budget}+{Command.Period_10_Budget})

But how do I add in that I want 2024 numbers for that portion and then add in the 2023 numbers I need?

So you need year and period logic to combine/aggregate the data across two consecutive years?

Try something like ...

NumberVar CurrentYear := Year(CurrentDate);
NumberVar PreviousYear := CurrentYear - 1;
NumberVar CurrentYearBudget := 0;
NumberVar PreviousYearBudget := 0;

If {@Period} <= 10 and {Command.Budget_yr} = CurrentYear then
(
CurrentYearBudget :=
{Command.Period_1_Budget} +
{Command.Period_2_Budget} +
{Command.Period_3_Budget} +
{Command.Period_4_Budget} +
{Command.Period_5_Budget} +
{Command.Period_6_Budget} +
{Command.Period_7_Budget} +
{Command.Period_8_Budget} +
{Command.Period_9_Budget} +
{Command.Period_10_Budget}
);

If {@Period} > 10 and {Command.Budget_yr} = PreviousYear then
(
PreviousYearBudget :=
{Command.Period_10_Budget} +
{Command.Period_11_Budget} +
{Command.Period_12_Budget}
);

NumberVar TotalBudget := CurrentYearBudget + PreviousYearBudget;

TotalBudget

Status
Not open for further replies.

Replies
7
Views
121
Replies
0
Views
145
Replies
2
Views
71
Replies
7
Views
122
Replies
5
Views
87