How to cumulate values in pivot table with empty cells

[PaulDODB]

Hello, I'm struggling for some weeks now to get the following problem solved, but with no success.

I have a table with accounting data, per general ledger and per date of booking.

Our fiscal year is from October 1 to September 30. 

On the first date of the first fiscal year, we made a booking of the capital of the company, 100K in this example.

I want to visualise the balance sheet for each fiscal year, but as there has been no booking on the capital account after the one on October 1, 2015, the pivot table doesn't accumulate the numbers from previous years.

See below example.

I've tried using the formulas " RangeSum(Before(Sum(Debit-Credit),0,ColumnNo())) " and " RangeSum(Before(Sum({Debit-Credit),0,ColumnNo(TOTAL))) but no success.

I also included the " SET NullValue = '0'; " and " NullAsValue *; " in the load editor, but still not possible to accumulate the data.

For the accounts with movements in all fiscal years, it's working fine. Problem is with the accounts without any movement in a given fiscal year.

Hope my problem is clear and understandable.

Can anybody help me with this please.

Thanks,

Paul

[brunobertels]

Hi 

See this post : 

https://community.qlik.com/t5/New-to-Qlik-Sense/Opening-Balance/td-p/1734963 

And example below from Gabriel

https://www.dropbox.com/sh/v5twzlr58gvt51t/AADR9Rwib4iPb1CPnU8HJP9Ja?dl=0 

You will find how to populate null values and repeat previous ones 

Hope it helps 

Regards 

[PaulDODB]

Hi Bruno, thanks for the information, gave me some insights in how to handle null values in Qlik Sense.

However, I didn't quite provide me with the solution. We have about 700 general ledgers, for 17 companies, for 5 years. Thus when I use the proposed solution, the system is calculating roughly 21 million lines of zero values. (each ledger a zero for each day of the fiscal year)

I basically only need a zero at the first or last day of each fiscal year.

Would appreciate if you have other ideas to share.

In the meantime I will keep trying the find the solution.

Regards,
Paul