Bank Account Balance

[hvfalcao]

Hello again!

How do I create a calculated field like this excel formula in Sense:

"Consulta" is the PivotTable name, witch have movements from a lot of bank accounts.

=CALCULATE(SUM(Consulta[BALANCE]);FILTER(ALLEXCEPT(Consulta;Consulta[ACCOUNT];CONTA[ACCOUNT]);Consulta[DATE]<MIN(Consulta[DATE])))

What this formula do:

- SUM the bank account BALANCE (credit - debit) WHERE DATE is less than the MIN selected date on the report, ignoring all filters (except the field ACCOUNT). This I call INITIAL BALANCE.

- In other words, I want calculate the running total of the bank account by the select period plus the INICIAL BALANCE.

If I was not clear enough let me know to explain better..

Thank you

[Anonymous]

Hello Henrique,

If my understanding is correct you are looking for something like this:

SUM( {< FIELD_1=, FIELD_2=, DATE = {"<=$(=date(min(DATE)))"} >} BALANCE)

where

FIELD_1 and FIELD_2 are fields you want filters to be ignorre (you would have to list all fields except ACCOUNT).

You might use the CONCAT function to achieve that automatically (search the community for the solution).

You might as well do it like this:

SUM( {1< ACCOUNT = P(ACCOUNT), DATE = {"<=$(=date(min(DATE)))"} >} BALANCE)

however I found it to work slower comparing to the first expression.

Please, give it a try and let me know if you have any further queries.

Best regards,

Janusz

[hvfalcao]

Hi Janusz,

Thank you for your help.

I could almost got what I want with this:

ABOVE(SUM( {1< ACCOUNT = P(ACCOUNT), DATE = {"<$(=date(MAX(DATE)))"} >} BALANCE))

The only problem is that it is still showing the Dates before my date selection... I just wanna see in the table the dates i´ve selected... Please check the print-screens bellow:

The calculated field with the above formula is "Saldo Anterior"

[Anonymous]

Hi Henrique,

Try this:

ABOVE(SUM( {1< ACCOUNT = P(ACCOUNT), DATE = {">=$(=date(MIN(DATE)))<$(=date(MAX(DATE)))"} >} BALANCE))

Regards,

Janusz

[hvfalcao]

Hi Janusz,

Unfortunately it didnt worked out...

What I need is to SUM the above BALANCE line, but just show on the table the selected dates on the filter... If I dont select the date, I need to SUM that line anyway (that is working), but dont show on the report (this is not working)

Thank you again!

[Anonymous]

Hi Henrique,

Could you explain again the logic of the date selection and the impact it has on the chart you are showing, please?

Best regards,

Janusz

[hvfalcao]

Janusz,

In my formula in excel I used that to calculate the PREVIOUS DAY END BALANCE.

After that I SUM the PREVIOUS DAY END BALANCE + TODAY BALANCE, and then I got the "RUNNING TOTAL" BALANCE..

Lets say that I have movements (credit and debit) in an account starting in 01/01/2000 and ending today:

DATE           CREDIT          DEBIT          TODAY BALANCE          TOTAL BALANCE

01/08/2014    100,00           50,00           50,00                              100,00

I don´t wanna show all dates from 01/01/2000 on the table/chart, I just wanna select and show todays movements and balance like this:

DATE           CREDIT          DEBIT          TODAY BALANCE          TOTAL BALANCE

01/08/2014    100,00           50,00           50,00                              100,00

But to get the total balance I need the previous dates running total balances (witch in this example is 50,00). I figured out yesterday, that I can do that, using RANGESUM:

RANGESUM(ABOVE(SUM({1<ACCOUNT = P(ACCOUNT), DATE = {"=$(=(date(MIN(DATE))))"} >} BALANCE),0,ROWNO()))

Unfortunately, I could not find a way, to consider the previous dates in the calc, without showing it on the table/chart...

That's what I need, consider previous dates (all dates) in the calc, but just show the selected dates on the table/chart...

Sorry if I could not explain better. Again... I appreciated your help!

Thank you,

Henrique

[Anonymous]

Hi Henrique,

I think the solution to your scenario is to pre-calculate START BALANCE for each date within a script.

Then in the final expression you will be simply adding TODAY BALANCE to it to calculate TOTAL BALANCE for dates from selected range.

It will use more space in RAM, however it will bring more simplicity to your expressions at the same time.

Hope it will work for you.

Best regards,

Janusz

[hvfalcao]

Hi Janusz,

I´m new to Sense/View, and don´t know how to do it... Any sugestions?

Thank you