Skip to Content

Cumulative Sum in Calculation View

Hi,

Just to explain my scenario, I'm trying to calculate a cumulative sum where I might not have transactional records for all days in question.

For example:

Day Customer Amount Cumulative Amt

1 ABC 10 10

2 ABC 5 15

4 ABC 4 19

In the example above, I had no transactions for day 3, and I get the correct cumulative amount in day 4. So it is working.

However, for the purposes of my requirement, what I actually need to see is what's displayed below:

Day Customer Amount Cumulative Amt

1 ABC 10 10

2 ABC 5 15

3* ABC 15

4 ABC 4 19

I actually want to see the accumulation not skipping day 3.

Although I don't have a transaction for day 3, I actually have day 3 in my model since I mapped it to a calendar view. Notice below (Image 1) day 01 and 08:

But when I do my cumulative amount, it skips the days with 0 quantities, so notice below (Image 2) for the blanked section, there's no value for 07, 08, 14, 15 etc.

Below is the scripted calculation view I'm using to determine the cumulative amount from image 2. It's taking the raw data from image 1

Any help is greatly appreciated.

Marcelo

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Jan 07, 2017 at 12:52 AM

    Hi Marcelo,

    You may use M_TIME_DIMENSION left outer join with your table, but applying the filtering on M_TIME_DIMENSION table to get the behavior you are expecting;

    I have simulated this with a different table and the solution may be of helpful to you;

    marcelo.png

    Notice that if I apply filter on POSTING_DATE_SAP, the Cummulation will be similar to the one you are getting.

    Cheers,

    Prasad

    Add comment
    10|10000 characters needed characters exceeded