I am having trouble solving an issue where I need to show the cumulative sum of transactions (account balance basically) on a trend line. The problem is that if I would want to see September 2020's full balance, it won't include customers who last transacted in August because it won't accumulative in September if there are no transactions in September.
What I tried doing was to create a full list of dates from 1999/01/01 up until today and then join it onto my transactions table with zero's and then concatenate the actual transactions to make 0 amounts for each date.
The problem with that is that the cartesian join then creates billions upon billions of lines which is not efficient and breaks the server CPU.
Currently, it looks like this:
MinMaxDate:
Load
LinkType,
MIN('1999/01/01') as [MinDate],
MAX(TODAY()) as [MaxDate]
RESIDENT DebtorsAging
GROUP BY LinkType;
TransactionsByDate:
Load
Date(iterno()+[MinDate]) as AgingDate,
LinkType
Resident MinMaxDate WHILE iterno()+[MinDate] <= [MaxDate];
OUTER JOIN (TransactionsByDate)
LOAD
CompanyName,
CustomerAccountCompany,
0 as Amount
RESIDENT DebtorsAging;
CONCATENATE(TransactionsByDate)
LOAD
CompanyName,
CustomerAccountCompany,
AgingDate,
Amount
RESIDENT DebtorsAging;
I would like to know if anyone has an idea of what other methods I can try to make zero dates for transactions without having to create so many empty dates (maybe a "Where Not Exists"?).
