Help with calculation...

[#1]

Hello all,

I have a table like this:

TransID, Date, TransType, Cash, Pending, Bal

01234, 20090101, Deposit, 1234, -,3234

01235,20090115, Withdrawal, -,34, 3200

01236, 20090129, Deposit, 12, -, 3212

I would like to sum up all deposits and all withdrawals per period (year, month etc), that is, sum(cash where transtype = deposit) and sum(pending where transtype=withdrawal) and use the Bal-value from the last transaction.

So my calculation would be something like vCal= sum(cash where transtype = 'deposit')-sum(pending where transtype='withdrawal')- Bal where max(transID)

In my example above that would be vCal=1246-34-3212 = -2000

How can I 'fetch' the value of Bal on the last transaction for the chosen period?

Observe that Im a beginner... 😄

Br

Cristian

[#2]

If your chosen period is selected, then you can use something like:

Sum({<TransID = {$(=Max(TransID))}>} Bal)

Since only one record will have the Max TransID value, that sum will give you that balance.

Your whole expression would be:

Sum({<TransType = {'Deposit'}>} Cash) -
Sum({<TransType = {'Withdrawl'}>} Cash) -
Sum({<TransID = {$(=Max(TransID))}>} Bal)

[#3]

You can try this.

Sum({<TransType = {'Deposit'}>} Cash) -
Sum({<TransType = {'Withdrawal'}>} Pending) -
Sum({<TransID = {$(=Max(TransID))}>} Bal)

This return de last TransID.

Sum({<TransID = {$(=Max(TransID))}>} Bal)

You can see the reference Manual Set Analisys.

Regrads.

Tonial.

[#4]

Thank you! This works! But, I forgot that I also would like do this calculation per AccountID. My table also have an AccountID field like this:

TransID, AccountID, Date, TransType, Cash, Pending, Bal

01234,123, 20090101, Deposit, 1234, -,3234

01235,123,20090115, Withdrawal, -,34, 3200

01236, 123,20090129, Deposit, 12, -, 3212

Would it be a different calculation? Still I will choose a period but the calculation should ne 'grouped' by accountId.

Br

Cristian

[#5]

Try with this:

Sum({<TransType = {'Deposit'}>} TOTAL <AccountID> Cash) -
Sum({<TransType = {'Withdrawal'}>} TOTAL <AccountID> Pending) -
Sum({<TransID = {$(=Max(TransID))}>} TOTAL <AccountID> Bal)

[#6]

Ok, I still have a problem.

The solutions suggested above didnt work (well they do but not as I want).

The 'Calculation' in the table below is exactly as the solution suggested by Mr Reaño. If I dont chose a date my calculation doesnt sum up as I want. I would like it to show, per each row (period) in the table," Calculation=Deposit-Withdrawal-Bal" where deposit= sum of deposits in the period, withdrawal=sum of withdrawals in the period, bal= value of field 'Bal' when max(transID) for the period. The current solution works only when a date is chosen and a single AccountID is present in that period. I would like it to work even if I dont choose an AccountID. Any suggestions are appreciated.

Thank you.

Br

Cristian

[#7]

Look this,
Sum({<TransType = {'Deposit'}>} TOTAL <YearMonth, AccountID> Cash) -
Sum({<TransType = {'Withdrawal'}>} TOTAL <YearMonth, AccountID> Pending) -
Sum({<TransID = {$(=Max(TransID))}>} TOTAL <YearMonth, AccountID> Bal)
Regards,

[#8]

I'm frustrated. It doesnt work. Is it my crosstable that is not working properly? If I choose AccountID and Year Month the calculation works fine.

[#9]

Ok, so for each accountID and Year Month it works fine. But, if I want to see the calculation only for a Month the calculation should sum all deposits, all withdrawals and each Sum({<TransID = {$(=Max(TransID))}>} BalCash) for every AccountID. Right now the calculation sums up all deposits, all withdrawals and takes only the last Sum({<TransID = {$(=Max(TransID))}>} BalCash) for the month.

How can I change Sum({<TransID = {$(=Max(TransIDr))}>} BalCash) to consider every AccountID when I dont choose a specific AccountID?

Regards,

cristian