Accumulate and calculate running numbers from ingoing balance and running change only (accounting)

[Martin_G]

Hi,

I'm working on financial KPI's and want to use the balance sheet as well as the P&L for analysis.

The differences is that in the balance sheet you have an ingoing balance and then the changes during the month is booked and then you get an outgoing balance, in my dataset there is no ingoing balances available from the database from any tables that I've found.

I want to to a rolling 12 months analysis on inventory turnover rate and need to be able to calculate averages for a twelve months period every month ending to get this KPI. 

I've added the ingoing balance for 2021-05-01 manually and concatenated with the transaction data, from this I can get an outgoing balance every month with RangeSum and Above where I've set period of times to 100000, (maybe there is a smarter way) which gives me the outgoing balance every month.

RangeSum(Above(Sum({<Sub_Group={'Inventory'}>}Amount),0,100000),)

How do I use this? I need to bring the value from 2021-05-01 to 2024-03-31 for example to be able to get my outgoing balance for 2024-03-31, see example below.

Is there a way to make a calculated field instead to use in set analysis or any other way that is more simple just shoot with ideas!

Help much appreciated, thanks!

[marcus_sommer]

Your record-check goes against kto but your shown Konto is only left(kto, 4) - if kto contained further granular information like an added date or similar the comparing of kto = previous(kto) may always return FALSE und preventing the accumulation. Therefore try to replace kto with Konto.

[Martin_G]

I've changed kto to Konto and tried all of the "new" number formats for and the old one for the field "bel", but still does not accumulate the amounts.

[marcus_sommer]

There is a typo by calling the field within peek() because Ack_belopp <> AckBelopp.

[Martin_G]

You spotted the problem Marcus, big thanks to you!

Next problem is to use the accumulated outgoing numbers in a rolling 12 month calculation, the Aggr(RangeSum(Above(Sum({<Inventory={1},YearMonth,Month,FY>}AckBelopp),0,12),),YearMonth)

 only calculates the running amounts for the account/kto. 

Do you by any chance have an idea for a script or set analysis to only get the outgoing value for the account in a separate field? 

[marcus_sommer]

An aggr() wrapping around the interrecord-function might be needed by an UI approach but at first skip it because of their complexity, means starting with something like:

RangeSum(Above(Sum({<Inventory={1},YearMonth,Month,FY>}AckBelopp),rowno(),12))

If later aggr() are necessary in regard to have multiple dimensions or extending an accumulation over the available chart-values and/or any further complications you will need to set each level within the right dimensional context and also applying there the set analysis.

But I think you may get many things more easier within the data-model, maybe by creating a running number of the account which is in a second step used as a boolean check, means:

if(Account = previous(Account), rangesum(peek('Nr'), 1), 1) as Nr

and the second step may something like:

if(Account = previous(Account),
           rangesum(peek('kumAmount', -1) * -(Nr>=1),
                            peek('kumAmount', -2) * -(Nr>=2),
                            peek('kumAmount', -3) * -(Nr>=3),
                            ....., Amount), Amount) as kumAmount

[Martin_G]

Good morning,

I got the Nr field to work, but it sets the numbers for all running transactions, this means it will calculate a the last 12 transactions rather than the outgoing balance for the last 12 months if i get this correct?

Also the second step got me an error message as below, even though the Nr field is working by itself without the second step. Should this be done in another resident load? I did use the same resident load as the accumulated numbers issue we previously discussed. 

Is it possible to set a flag for each account and max date of the YearMonth? Since the last transaction can be 2024-04-28 instead of 2024-04-30 it is not possible to use the MonthEnd function I've noticed.

The following error occurred:

Field 'Nr' not found

The error occurred here:

Transaktionsdata: Load *,

RecNo() as RecNo, RowNo() as RowNo,

if(Konto = Previous(Konto),

RangeSum(Peek('AckBelopp'),Ny_belopp_ver2),Ny_belopp_ver2) as AckBelopp,

If(Konto = Previous(Konto), RangeSum(Peek('Nr'), 1),1) as Nr,

if(Konto = Previous(Konto),RangeSum(Peek('AckBelopp2',-1) * -(Nr>=1),

Peek('AckBelopp2',-2) * -(Nr>=2),

Peek('AckBelopp2',-3) * -(Nr>=3),bel)) as Ackbelopp2

Resident Rullande12 order by Konto, Datum asc

[marcus_sommer]

If there are n records per period you couldn't apply such fixed approach with going back max. 12 records in dependencies to further conditions. Probably there are also ways to make such logic dynamic but I think there are other respectively extended methods more suitable.

Extended could mean to mix different granularities within the facts - one is atomic and the other is aggregated on the period-level and on this the interrecord-stuff could be applied:

Fact Table with Mixed Granularity - Qlik Community - 1468238

Your error is caused from calling a field in the load which is created in the same. With the mentioned following steps was a resident-load and/or preceding-load meant.

Beside this I think you may need more as a single approach go get the different accumulation-views because normal accumulations and rolling ones and those with considering start/end balances are different challenges. A quite good overview to the various possibilities could you find here:

Calculating rolling n-period totals, averages or o... - Qlik Community - 1483033