Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
i have a question regarding cummulation of values with different dimensions. Database is a list with changes. I want to calculate the cumulative value for the different dimensions. Please see the dataset
DAY | MANDT | PORTION | CHANGE | CUMM |
---|---|---|---|---|
01.01.2009 | 100 | ABCDE | 10.000 | 10.000 |
01.01.2009 | 100 | VWXYZ | 5.500 | 5.500 |
02.01.2009 | 100 | ABCDE | 100 | 10.100 |
02.01.2009 | 100 | VWXYZ | 5 | 5.505 |
03.01.2009 | 100 | ABCDE | -10 | 10.090 |
03.01.2009 | 100 | VWXYZ | -3 | 5.502 |
04.01.2009 | 100 | ABCDE | 30 | 10.120 |
04.01.2009 | 100 | VWXYZ | 6 | 5.508 |
05.01.2009 | 100 | ABCDE | -40 | 10.080 |
05.01.2009 | 100 | VWXYZ | 6 | 5.514 |
06.01.2009 | 100 | ABCDE | 20 | 10.100 |
06.01.2009 | 100 | VWXYZ | 8 | 5.522 |
07.01.2009 | 100 | ABCDE | 30 | 10.130 |
07.01.2009 | 100 | VWXYZ | 5 | 5.527 |
08.01.2009 | 100 | ABCDE | -40 | 10.090 |
08.01.2009 | 100 | VWXYZ | 3 | 5.530 |
My question is, how can i calculate the correct cumulative value within the comlete dataset, even if i selected a specific date (07.01, 08.01), in other words calculate the correct cumulative value (without selection) for specific dimensions from start of dataset till selected date (s.th like sum ({$<date >= (date (01.01.2009, DD.MM.YYYY)>} Changes) ?? ). The result should look like this with selection of 07.01.2009 and 08.01.2009:
DAY | MANDT | PORTION | CHANGE | CUMM |
---|---|---|---|---|
07.01.2009 | 100 | ABCDE | 30 | 10.130 |
07.01.2009 | 100 | VWXYZ | 5 | 5.527 |
08.01.2009 | 100 | ABCDE | -40 | 10.090 |
08.01.2009 | 100 | VWXYZ | 3 | 5.530 |
Thank you very much for your help!!
Greetz
has no one a hint in which direction I can search? - Please
Thank you very much!
Hi,
Use a variable in a slider/calendar object instead of selecting in the field. Then use this variables in the set analysis as in this application.
Hope that helps.
Miguel
Hi Miguel,
thank you very much for your answer. Your file is very helpful.
I used the Date Variables (vStartDate and vEndDate) to select the changes. For the Sum (cumulative) i used the whole time period. The problem is that the Dimension (Days) is shown for the whole period.. For the selected time period (vStart.. till vEnd..) the changes have the value 0.
In addition the cumulative sum is not correct. Do you have an idea what the problem might be.
Thank you so much!
Jezze
Hi Jezze,
Not sure if I got you right. Check the ammended file though.
Hope that helps.
Miguel
Hi Miguel,
thank you for your answer. Maybe the cumulative sum is not working for more than one dimension..
Your formula "RangeSum(Above(Sum(SalesAmount), 0, RowNo()))" is a good idea and shows the same result like using the radio button "Accumulation" in the formula register.
I´m sorry that i did not explained clearly how the final table should look like. It would be great if following requirements could be met or ideas how i can handle them.
I´ m really excited for your ideas.
Thank you very much for your help.
Greetings
Jezze
Hi Jezze,
Indeed I was wrong. Is that what you are looking for?
Miguel
Hi Miguel,
that looks great, thank you,..
There is only one functionality missing - but i see, i didn´t explain it very well - i´m very sorry for that.
The "Cumm" columns should be calculated as a sum of all values of the "Change" columns from the first day of dataset till the selected date range.
The "Cumm" column should not be a part of the original dataset. The dataset should only consist of Day, Mand, Portion and Change. (I just wanted to show it to explain how the values are calculated).
Best greetings
Jezze