Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to select from 2 rows in a table

Status
Not open for further replies.

JustMe24

Technical User
Jul 18, 2024
1
0
0
US
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?
Thank you in advance.
 
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.

Part and Inventory Search

Sponsor

Back
Top