Skip to Content
0

Cumulative Sum in Calculation View

Jan 05, 2017 at 05:47 PM

450

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Prasad AV Jan 07, 2017 at 12:52 AM
0

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


marcelo.png (58.3 kB)
Share
10 |10000 characters needed characters left characters exceeded