# How to apply logic where we can cumulate the values of list items without time?

Options

Hello Everyone, Can you please help me out in applying a logic where we can cumulate the individual List item. Example: if a person is having 5 sales that he does sale 1 has some revenue, sale 2 has some revenue same as for Sales 3,4,5. In our desired line item: sale 2 value should be the cumulative Revenue of Sale 1 and 2, Sale 3 should be cumulative revenue of Sale 1,2 and 3 and so on, Thanks in advance

«1

• Options

Have you checked out the function cumulate?

• Options

Yes I did, But it was not cumulating the List items under one parent, I need a function/logic where we can cumulate the child items under a single parent, for child 2 it should sum the values there for Child 1 and itself, for child 3 the value should be child 1+child 2+ itself.

• edited August 2023
Options

That is what Cumulate does using the 3rd parameter

• Options

This is the requirement, if I use cumulate its only summing up the quarter values but not for the child, Any other logic works?

• Options

See if this works for you.

My baseline data, which should look like yours

In a SYS Module based off the row axis above (mine is New CC), you will need to create a mapping to the previous member. I took the easy way and did this by code, but you will need to figure out what works best for you.

In another module, with the row axis (New CC) and now dimensionalized just at the Year level…

I created a formula that does a lookup to the previous member's value and add it to the current value.

Does that work for you?

• Options

Hi Rob,

Thanks for Sharing the Information, will definitely try this out and I think I got the solution.

• Options

Hi Rob, I checked in my system but the problem is we don't have a code that is only numbered format, The code that we have for particular list is combination of text and numbers, so If i add up the +1 value for the code we have it is returning a NAN format, can you please help me out regarding this logic.

• Options

How many members in the list?

• Options

Hi, A single member has almost 30-40 Sales or more, that he/she Does and there are almost more than 300 members.

Here: member is Parent, sale he/she does is the child item that(Sale) has code as the combination of number and text formats.

• Options

Please post a picture, it will help.

• edited August 2023
Options

The left side ones are the Employees under them are the sales they make, code is the combination of text and numbers.

• Options

And you are wanting a running total at the parent (Abhi in the picture)?

• Options

Hi Rob,

No, i want to get the cumulate value for each sale, for Abhi in the picture for suppose if he is having 10 sales, 1st sale value is \$ 100, second sale value is \$ 200, 3rd sale value is \$ 400, so revenue should be as follows: for 1st sale: \$ 100, 2nd sale: \$ 300(which is cumulating sale 1 and sale 2), 3rd sale: \$ 700( cumulative of sale 1,2 and 3), and so on,(Note: This also includes timescale at quarter level so at yearly level for each sale we need the cumulative value)

we are not able to achieve this by the example because each sale is having a code which is combination of text and numbers.

• Options
• Options

Does this not work for you?

• Options

Hi Rob,

I see this formula is working but can you please elaborate what it does?

• Options

It is cumulating down the rows and summing to the year. The first picture is just data (Revenue). The second picture is doing the cumulation down the row.

• Options

Thanks Rob, I am able to get the data accurately

• Options

Hi Rob,

I applied the logic as said but the values are some random numbers, they are not according to the requirement. attaching the screenshot below.

for Abhi in the image, when is doing the 1st sale it should ideally show the value as 10,729,xxx in cumulate line item but even for the 1st sale he does its showing 1665,xxxx which is not accurate, and also for the 2nd sale requirement is to add 10,729,xxx and 1,703,xxxx which will be 12,43x,xxx but we are getting it around 2,415,xxxx which is again not working for 2nd sale and same for all the other sales, can you please check this out.

the formula I applied for cumulative line-item is: cumulate(sales, false, [list name that has sales]) and summary as : none, time-sum.

• Options

Check the 2nd parameter in the cumulate function.

• Options

This is the logic that I used for cumulate lineitem

• Options

It really hampers trying to help you when you keep blocking out pieces of the formula or the data, it is not like you are blocking out information that people can actually use to get a competitive advantage. In the Anapedia link I linked above, please review the 2nd parameter and have it reset at the parent.

• Options

Hi Rob, Its the confidential information that we are not allowed to share, so I am hiding part of the numbers and names of the employees, as such i am trying to give the explanation under the image for the information hidden, can I help you out with any other information for working on this?

• Options

The list name is confidential information? Interesting…Did you change the 2nd parameter from FALSE to TRUE based on the parent as I stated above?

• Options

Hi Rob,

I got you, Yes I changed the 2nd argument to "True", but it is just showing the values without cumulating.

Sales is the Line-item which shows the employee sales that he makes, cumulate logic that I applied is in 1st image and [ATX] assigned employee transactions is the list that has all the sales they make having employees as the parent.

• Options

Like this?

Final result:

In order to do that, you need to create the RESET flag to be the first member under that parent. To do that, in your SYS module of that list (if you don't have one, create one) and create these line items.

• Options

Hi Rob, I am still getting the same for 2nd sale onwards,

Formula for cumulative is:

Also, will the formula be effected if there is another list as a dimension?

if I drill down on this the further results are as below images:

• Options

Please review the 2nd parameter, what is the formula for the reset? line item?

• Options

Hi Rob, Its the same formula (Reset? = Rank =1). what should be the 2nd Parameter?

• Options

Well, something is different if you aren't getting the same results. Again, a picture would do wonders.