Happy Business Starts Here

Re: Cumulative Sum of Invoice Items

Highlighted
Senior Tutor

Cumulative Sum of Invoice Items

Hi, 

I am new to Workflow tasks, but this is what i was trying to achieve. I want to run through a set of Invoice Items and add the balances, and see if the balance is less than a Value. In Order to achieve this, I have initiated a Liquid variable before the iterate Invoice Items to 0 and Adding it after the iterate task in a Conditional Task using the below 

{% assign currentinvoicebalance = Data.Liquid.currentinvoicebalance | plus:Data.LinkData.Balance | plus:0.0 %}

However this currentinvoicebalance variable is only showing the 0 always for each iterate instead of keeping the running total. Any idea how this can be achieved? Thanks.

5 REPLIES 5
Highlighted
Master

Re: Cumulative Sum of Invoice Items

I suggest you achieve the cumulative sum of invoice items by using a Logic (Liquid) task and iterate through the collection of the data objects. 

 

Highlighted
Senior Tutor

Re: Cumulative Sum of Invoice Items

Yes, ended up doing the same after some research.

I have to do a data query to get the invoice items i need, and then convert to JSON objects and then do the liquid, and then do the iterate of objects to achieve what I intended to do. Thanks for the reply, but i was wondering why this wasnt possible with simple data query and iterate.

Highlighted
Student

Re: Cumulative Sum of Invoice Items

We require a sample workflow design where we can get the cumulative sum of all the invoices posted under a bill run to be populated in a custom field at subscription rate plan charge level.
For example consider a bill run is being posted for a customer account . The bill run has 5 invoices posted. I would like to see the sum total amount of all 5 invoices being populated at a custom field
This should happen through workflows

Highlighted
Master

Re: Cumulative Sum of Invoice Items

@karuv Start with building a data query to get the sum total amount of the invoices with the criteria you have. Use the Data query UI to test this part. Once you have the query finalized, test it in a workflow by adding -

1) Retrieve: Data query task - use your query 

2) on success of step 1 add Logic: Liquid task to read the cumulative sum value from the Data.LinkRun to a Liquid variable.  (do not prefix the variable name with an underscore else the scope will be limited to this Logic task) 

3) this custom variable will be available now

 

these are a high level steps to get what you need. Hope that helps.

 

Tags (2)
Highlighted
Zuora Staff
Zuora Staff

Re: Cumulative Sum of Invoice Items

You could consider using window functions - https://prestodb.io/docs/current/functions/window.html where you specify the cumulative sum grouping with the "over" clause.