Forum

This content is now out of date.

Visit Our Community

Cumulative Total – ignore date

Hello,

There is any way to create a calculated field, like a cumulative total from all records, ignoring the date period?

I'll try to explain better what I want...

I have a table like this (bank account movement):

DATE | DEBIT | CREDIT
1 | 10,00 | 0,00
2 | 0,00 | 20,00
3 | 0,00 | 10,00
n | n | n


I've created a new calc field named TOTAL (credit-debit):


DATE | DEBIT | CREDIT | TOTAL
1 | 10,00 | 0,00 | -10,00
2 | 0,00 | 20,00 | 20,00
3 | 0,00 | 10,00 | 10,00

Lets suppose that DATE is a year, and I've created a new report, that just show me YEAR 3 movements/balance:

DATE | DEBIT | CREDIT | TOTAL
3 | 0,00 | 10,00 | 10,00

What I really need is another column (BALANCE) showing me the actual balance, considering YEAR 1, 2 and 3 balances:

DATE | DEBIT | CREDIT | TOTAL | BALANCE
3 | 0,00 | 10,00 | 10,00 | 20,00

or

DATE | DEBIT | CREDIT | TOTAL | PREV DATES BALANCE | ACTUAL BALANCE
3 | 0,00 | 10,00 | 10,00 | 10,00 | 20,00

I've create this in MS excel using the FILTER ALLEXCEPT function, witch sum the total balance where DATE < min(DATE):
=CALCULATE(SUM(Consulta[BALANCE]);FILTER(ALLEXCEPT(Consulta;Consulta[Conta Corrente];CONTA[Conta Corrente]);Consulta[DATE]
I'm evaluating YF 7.1. Firebird 1.5 DB (not showing date hierarchy options when creating a view).

Thank you
Henrique


That excel formula was created to get the Previus Balance...
After that I SUM(PREVIUS BALANCE+ACTUAL BALANCE).
Hi Henrique,

that sort of requirement is handled very well by append sub-queries (with the sub-query having a different time filter than the master query), however, I'm aware that you are having problems with append sub-queries with Firebird 1.5 so I hope you are able to upgrade your Firebird installation to version 2.5 to resolve the append sub-query issue.

Please let us know how it goes.

regards,
Dave
Hi Dave,

The problem with the sub-query with FB 1.5 will be fixed in YF or it is a about FB 1.5 capabilities to handle it?


Thank you again!
Hi Henrique,


I don't know the finer details of what the developer found when he investigated this a few years back, and unfortunately he is on holidays at the moment so I can't ask him. However, I would take a guess that because we don't have many clients who use Firebird that it would be unlikely we'll be able to allocate time and resources to investigate that older version of Firebird.

Are concerned about the risk of upgrading Firebird from 1.5 to 2.5? If you are then I did some research and found this page that explains the way to do it is to go from 1.5 to 2.1 and then 2.5 and run some scripts. And of course do it in a test environment first.

regards,
Dave
Hi Dave,

Thanks again. I've migrated a copy of our DB to fb 2.5 in a test environment for evaluate YF (as we run our ERP's in this DB, I can't migrate it from the day to the night without running a lot of tests...)

The good news is that I was able to run the append subquery.

The question now is about the Date Hierarchy.. I've created a new view and I could find the Date Hierarchy fields.

Thank you

Hi Henrique,

yes, smart move to upgrade your Firebird in a test environment! and great to hear that the Yellowfin sub-queries are now working because of the Firebird upgrade.

Regarding the Date Hierarchy issue, as described in this other forum post, we have implemented the Date Hierarchy fields for most of the popular databases however at this point in time not for some of the less-popular databases. So I have raised an enhancement request (Support Task 183002) to do this for Firebird data source.

In meantime it should be possible to create your own Date Hierarchies using the Freehand SQL Calculated Field, for example, if you wanted a Year Date Hierarchy then just use the DatePart() SQL function in a Freehand SQL Calculated field.

regards,
Dave
Hi Dave,

Thank you for your support again!

Henrique

Thanks for trying Yellowfin

Please complete the form below to request your copy of Yellowfin today.