How to apply logic where we can cumulate the values of list items without time?
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
Answers
-
Have you checked out the function cumulate?
0 -
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.
0 -
0
-
This is the requirement, if I use cumulate its only summing up the quarter values but not for the child, Any other logic works?
0 -
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?
0 -
Hi Rob,
Thanks for Sharing the Information, will definitely try this out and I think I got the solution.
0 -
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.
0 -
How many members in the list?
0 -
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.
0 -
Please post a picture, it will help.
0 -
The left side ones are the Employees under them are the sales they make, code is the combination of text and numbers.
0 -
And you are wanting a running total at the parent (Abhi in the picture)?
0 -
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.
0 -
0
-
Hi Rob,
I see this formula is working but can you please elaborate what it does?
0 -
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.
0 -
Thanks Rob, I am able to get the data accurately
0 -
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.
0 -
Check the 2nd parameter in the cumulate function.
0 -
This is the logic that I used for cumulate lineitem
0 -
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.
0 -
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?
0 -
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?
0 -
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.
0 -
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.
0 -
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:
0 -
Please review the 2nd parameter, what is the formula for the reset? line item?
0 -
Hi Rob, Its the same formula (Reset? = Rank =1). what should be the 2nd Parameter?
0 -
Well, something is different if you aren't getting the same results. Again, a picture would do wonders.
0
