6 Replies Latest reply on Oct 7, 2016 6:35 AM by philmodjunk

# I need a better solution than using Nth record

I have a table of financial records that contains thousands of records of account balances and the date of that balance for hundreds of clients. Each record represents an account balance for only the last day of each month.

Example: MONTHLYBAL (table)

PK: 101, Balance: 1,590, Date: 6/31/2016

PK: 101, Balance: 1,620, Date: 7/31/2016

PK: 101, Balance: 1,680, Date: 8/30/2016

PK: 101, Balance: 1,896, Date: 9/31/2016

PK: 102, Balance: 2,250, Date: 6/31/2016

PK: 102, Balance: 2,490, Date: 7/31/2016

PK: 102, Balance: 2,870, Date: 8/30/2016

PK: 102, Balance: 2,944, Date: 9/31/2016

(etc)

Every quarter I need to run a report that will calculate the amount of change between the current end quarter month (i.e. 9/31/2016) and the previous quarter ending month (i.e. 6/31/2016). I need a calculation that will grab the account balance from the last quarter end month (skipping the other non quarter ending months) and subtract the amount of the previous quarter from the amount of the current quarter.

• ###### 1. Re: I need a better solution than using Nth record

Set up a calculation field that returns date that calculates the previous quarter date:

Let ( [ D = Day ( Date ) ;

M = Month ( date ) ;

y = Year ( date ) ];

date ( D ; M - 3 ; Y )

) // this calculation will work even when M = 1

Then set up a self join relationship that matches by PK and this calculated date field to PK and Date. That will give you a direct link to the record for the same account from the previous quarter.

• ###### 2. Re: I need a better solution than using Nth record

You need adjust for day. 6/31 results 7/1. Using year-month field make it easier.

OP typoed on 6/31 and 8/30, should be 6/30 and 8/31.

• ###### 3. Re: I need a better solution than using Nth record

User19752 has a good point. Somehow I was thinking the first of the month, not the last day of the month.

Assuming that the date is always on the last day of the month...

Let ( [ dt = Date + 1;

D = Day ( Dt ) ;

M = Month ( dt ) ;

y = Year ( dt ) ];

date ( D ; M - 3 ; Y ) - 1

)

This calculates 3 months back for the next day, the first of the next month, then backs up one day to land back on the last day of the month.

• ###### 4. Re: I need a better solution than using Nth record

wouldn't the last day of any month be:

Date ( M+1 ; 0 ; Y )

day '0' of the next month is the last day of the given month (accounts for leaping days and years automatically & crossing year boundaries)

Date ( 13 ; 0 ; 2016 ) // 31 DEC 2016

Date ( 1 ; 0 ; 2016 ) // 31 DEC 2015

beverly

1 of 1 people found this helpful
• ###### 5. Re: I need a better solution than using Nth record

All this time I've been using Date ( M + 1 ; 1 ; Y ) - 1 to get the first last of the month... you just saved me 2 keystrokes!!!

• ###### 6. Re: I need a better solution than using Nth record

Yes, use it all the time. I didn't do that here simply because the data shown already has the last day of the month, but I'd certainly include that detail if the original data isn't always the last day of the month.