Cumulative sum with the previous value

[mnase11]

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

[rubenmarin1]

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

[mnase11]

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)))
"

[rubenmarin1]

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.

[mnase11]

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

[rubenmarin1]

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;

[mnase11]

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 

[MeehyeOh]

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!

[rubenmarin1]

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.