Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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