Cumulative Sum in Calculation View

[celo_berger]

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

[Former Member]

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