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];
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"?).
