Filling in missing records

FGS-Digital

Forums

Hi everybody,
 
I have table A with columns "CustomerNo.", "Period" and "Amount" that has ~ 5M records.I need to calculate a rolling total over a period of 12 months before "Period".
Problem is that I don't have one record per Customer per Period, but I am missing months for most customers.
Is there a smart way of joining the data in IDEA to create the missing records and let me calculate the Rolling Total (https://www.ideascripting.com/forum/running-total)
Any suggestions would be greatly appreciated.
cheers
 
PS: added my current code to calculate the running total for each Customer (only if file is sorted accordingly, e.g. by summarization)

Brian Element Wed, 01/18/2023 - 09:26

Hi there, sorry I seemed to have missed this post.  Can you give an example of what you are trying to create.  I am not sure I understand why you want to create the missing records.

Have you checked out the SmartAnalzyer Utilities app that you can find in Passport?  It has a utility called Accumulated Total that does the following: