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!

[Chanty4u]

Try this 

RangeSum(

    Above(

        Sum({<Sub_Group={'Inventory'}>} Amount),

        0,

        100000

    ),

    Ingoing_Balance 

)

For avg 

Avg(

    {$<Date={">=$(=Date(AddMonths(Max(Date), -11), 'YYYY-MM-DD'))<=$(=Max(Date))"}>} Running_Numbers)

[Martin_G]

Hi Chanty4U, thanks for your reply.

I can't get your expression to work, and from my basic knowledge (since I'm new to Qlik) there is a problem with the date expression since the -11 for AddMonths only will give me the latest 12 months while the ingoing balance is a fixed number as of 2021-05-01 - see example in Excel below and the Excel file attached, and the transaction amounts are the change to this account.

You will need to accumulate all transactions from 2021-05-01 all the time to get an outgoing balance for each month end and this outgoing balance is what I need to use in a calculation for Rolling average over 12 months.

It's kind of hard to wrap your head around and maybe I will need another calculated field or something like that to forward the amount every month or something like that? All help is much appreciated!

[marcus_sommer]

For simple accumulations are the UI interrecord-functions like above() quite useful but they relate only to the existing cell-values. If there are more advanced requirements in regard to the number of dimensionalities and/or specialized conditions it becomes complex and requires usually one or more aggr() layer to get the wanted views.

Therefore I suggest to transfer as much logic as possible into the data-model, for example applying there the accumulations with the script interrecord-functions of peek() and previous() within a properly sorted resident-load - maybe something like:

load *,
        if(Account = previous(Account),
           rangesum(peek('kumAmount'), Amount), Amount) as kumAmount
resident X order by Account, Date desc;

[Martin_G]

Hi Marcus,

Is it possible to add the acc amount in a separate field in the existing fact table?

As the example above, I've concatenated my manually added ingoing balances with the transaction data.

I added this in the script where kto = account, bel = amount and got the result in the picture below.

if(kto = Previous(kto),
RangeSum(Peek('Ack_belopp'),bel),bel) as Ack_belopp,

There is no accumulation of the amounts in the "Ack_belopp" field and it is even missing the manually added ingoing balance. Do you have to do it as resident load? And in that case

[marcus_sommer · Accepted solution]

Like hinted above it must be a sorted resident load to ensure that the right order is applied. For the development stage you may add the record-information to the load and using them within the table-box to track if the intended order was applied, means something like:

load *, recno() as RecNo, rowno() as RowNo,
        if(Account = previous(Account),
           rangesum(peek('kumAmount'), Amount), Amount) as kumAmount
resident X order by Account, Date desc; 

[Martin_G]

It's my fault since I'm trying to learn and do not fully understand, Marcus.

I've added the code in my script as a resident load and it seems as the order by works but it's not accumulating the numbers in the created field. Below is an attached image of a shortened script for the fields connected to the issue. The table "Ack_BR" is my try of the code you wrote above and image number 2 is the result in front end.

It doesn't accumulate numbers, just repeating them right now, if it's possible, one field with the out going number would be preferred for easier calculations.

Can you identify where I'm doing it wrong?

Translation:

bel = Amount

kto = account

trdat = Date

[Martin_G]

I also have a master calendar made, this is a picture of the data model, I also get a Syn Key for the new table Ack_BR.

[marcus_sommer · Accepted solution]

The new table with the accumulation is a replacement for the old table which must not remain else needs to be deleted - means to add:

drop tables Rullande12;

after the resident load whereby I wouldn't suspect this as cause for the missing accumulation but it may lead to wrong visualizations and calculations.

If dropping the table and removing all synthetic keys doesn't help take a look on the amount field if it's really interpreted as number - maybe just double the field in the both concatenate-loads with something like:

num(bel) as new

and if it failed and returning NULL then

num(num#(bel, 'FormatPattern')) as new

Another look may go to any qualify-statements in the script - are there any then comment them.

[Martin_G]

I've tried both ways with the amount field but no look, still does not accumulate numbers.

I guess I was supposed to use the "new" amount field with the format in RangeSum(Peek()) function, but still no luck, see below for script and result.

No qualify-statements are used in my script anywhere.