Beginning/End of Period Balances

[#1]

I have a need to calculate Beginning/End of Period Balances and cannot seem to get all of the numbers right. As my source data I have a table containing the following fields:

TRANS_ID - Primary Key
PRE_TRANS_BAL - Account balance prior to the transaction
TRANS_AMT - Amount of transaction
TRANS_DATE - Date of transaction
TRANS_MONTH - Month of transaction
POST_TRANS_BAL - Account balance after the transaction
CUST_ID - Customer ID

Currently I have the following and it works as long as the customer has a transaction every month, but not every customer does. If they don't their balance is not inlcluded in the sum for that month along with the customers that had transactions, and this is my problem.

Sum(Aggr(FirstSortedValue(PRE_TRANS_BAL, TRANS_ID),CH, TRANS_MONTH))

An example of what I would like to do is something like this: Sum( Aggr( Only({$<TRANS_ID={"= Max({$<TRANS_MONTH<=TRANS_MONTH>} TRANS_ID)"}>} PRE_TRANS_BAL ), CUST_ID, TRANS_MONTH))

Obviously the above doesn't work or I wouldn't be posting this as a question, so in english what I need is Sum of PRE_TRANS_BAL where (TRANS_ID = the MAX TRANS_ID where ( TRANS_DATE <= the TRANS_MONTH dimension on the chart) )

I have considered generating blank records in the load but there are around 45,000,000 customers and 1,000,000,000 transactions so adding blank rows to the model to make the calculation easier on the front-end could be a very large load to run.

[#2 · Accepted solution]

Something like this.

[#3]

The result is correct, but unfortunately I need to accomplish the same result using chart expressions and not load script changes. Does anyone know of a way to reference the charts dimension value in $ sign expansion. I was trying something similar to the expression below but cannot get it to work since QlikView seems to ignore the chart dimensionality inside the $ sign expansion. Even if I could build the expression as a string and then execute it that would work as well. But from my understanding of the help documentation the Evaluate function does not work inside chart expressions.

Sum( Aggr( Only( {$<TRANS_ID={"$(= Max ( {$<TRANS_DATE={"<= $(= [Month Dimension] ) "}>} TRANS_ID) )"}>} PRE_TRANS_BAL ) , CUST_ID, TRANS_MONTH ) )

[#4]

There is no way to have dollar sign expression if you need to treat each row individually. You should use IF conditions.

In my example you don't need to reload your transaction data. All what you need is just create something like specific Month Calendar for Balance by this code:

BalanceCalendar:
load month(addmonths(date#('1/1/2010'), (rowNo() - 1) )) as TRANS_MONTH
autogenerate 12;

left join (BalanceCalendar)
load *
 ,month(addmonths(addmonths(date#('1/1/2010'),num(TRANS_MONTH) - 1), (iterNo() - 1) )) as BalanceMonth
resident BalanceCalendar
while (num(TRANS_MONTH) - 1) + iterNo() <= 12;

It's not more than a 100 records for whole application. it's much less than thi:

apettit wrote:I have considered generating blank records in the load but there are around 45,000,000 customers and 1,000,000,000 transactions so adding blank rows to the model to make the calculation easier on the front-end could be a very large load to run.

Anybody here will tell you that this is make sense to enrich a data to get advantage of it for simplifying an expression.

Anyway it's up to you.

[#5]

Thanks,

I had to make some modifications because the customer file uses more dynamic values than begin/end of month, but the concept worked. I can now give you the balance of any customer or group of customers on any given date.