topic Re: Cumulative sum with the previous value in App Development

Cumulative sum with the previous value

mnase11 — Sun, 22 Oct 2023 07:08:33 GMT

Hi,

I have just ran into an issue where i need to do cumulative sum keeping in mind the previous balance as well. I am able to do it on the front end but unable to replicate it in the load script. Below is a description of how to achieve it

So i have projects which come every day with a new unbilled amount and it might be in the same case the amount is than cleared as well, but there is always some left over balance which need to cumulated on every level of field such as Year, Month, Business Unit which is why i need it in the load script. in addition if someone selects a period such as 2022 Jan - it should have the last balance of 2021 - Dec or else the balance will be calculated wrongly.

I have attached the sample data and the expression that i have used in the front end

Re: Cumulative sum with the previous value

rubenmarin — Sun, 22 Oct 2023 18:01:21 GMT

Hi, to replicate Above() in script you can use Peek() or Previous().

Full accumulation could be:

LOAD New_Unbilled, Cleared, ... Other fields, Rangesum(New_Unbilled, Cleared, Peek(AccumulatedValue) as AccumulatedValue From ...

You can add and order by and check with if() to accumulate by project or other fields, like:

LOAD New_Unbilled, Cleared, ... Other fields, If(Project=Peek(Project) ,Rangesum(New_Unbilled, -Cleared, Peek(AccumulatedValue) ,Rangesum(New_Unbilled, -Cleared) ) as AccumulatedValue Resident ... Order By Project

Re: Cumulative sum with the previous value

mnase11 — Mon, 23 Oct 2023 10:12:29 GMT

Dear Rube,

The above is not working correctly, i might be doing something incorrect surely, i have attached what i have done in the front end as an excel, i am trying to calculate ERP Balance field in the backend. Probably i was not able to able to clarify myself.

And this is the formula that i have used to calculate ERP Balance

rangesum(Above(total Sum(New_UnBilled-Cleared),0, RowNo(total)))
"

Re: Cumulative sum with the previous value

rubenmarin — Mon, 23 Oct 2023 11:59:33 GMT

Hi, I already saw that Excel file, if something fails I will need to check the qlik sense app, paste the script where it is used. It will be better if you can add a sample app with non-real data to make some tests.

Re: Cumulative sum with the previous value

mnase11 — Mon, 23 Oct 2023 12:12:52 GMT

it has an extra field added of what i am trying to achieve in the back end

the balance field has the previous balance in mind and than adding the new unbilled & if there is cleared it will subtracted as well

Re: Cumulative sum with the previous value

rubenmarin — Mon, 23 Oct 2023 15:06:22 GMT

Hi, this script creates the ERP Balance column:

tmpData: LOAD [Project #], [Year], [Month], [ERP Cleared], [ERP New Unbilled] FROM [lib://DataFiles/Calculating Unbilled.xlsx] (ooxml, embedded labels, table is Sheet1); FinalData: NoConcatenate LOAD *, RowNo() as NumFila, Rangesum([ERP New Unbilled], -[ERP Cleared], Peek('ERP Balance')) as [ERP Balance] Resident tmpData; DROP Table tmpData;

Re: Cumulative sum with the previous value

mnase11 — Tue, 24 Oct 2023 07:45:28 GMT

Hi it does create the Balance field but once we do the validation its completely wrong as for example the balance for the Jun-2019 should be 432,779,246 but the above code is returning 89,331,234.

As for convenience i have attached a sample qvf of the data and in the front end a basic table of what i am doing

Re: Cumulative sum with the previous value

MeehyeOh — Wed, 25 Oct 2023 00:25:18 GMT

Hi, @mnase11

I want to check before test.

Is it the cumulative amount of the entire period per project# that you are trying to calculate?

Or is it accumulated over the entire period regardless of Project#?

please reply!

Re: Cumulative sum with the previous value

rubenmarin — Wed, 25 Oct 2023 06:02:32 GMT

Hi, note that the accumulation is done like in the excel: mixing months and years.

Apply 'Order by' on the FinalData table to accumulate by year, month, and any other fields you want to sort this accumulation.