SAP Analytics Cloud Accumulated Sum for specific Dimension

[liskb]

I have seen where I can add a Calculation on a column like Accumulative Sum, but this will create a running total for all rows in the table. I want the running sum to reset for every new member of a dimension like Material Number Similar to the example below.

I have looked through other posts and have not found a solution for this or way to control when the accumulative sum resets by a dimension member.

This is what I want, where Accumulative Qty resets with each new material number

This is what I get where the running total keeps accumulating across all rows regardless of material number.

[liskb]

Also, I am using an Analytic Dataset from a live DWC connection.

[William_Yu]

Hi,

Maybe you can try to use show total by Material number dimension, right click on dimension header of meterial number -> show/hide -> total.

[liskb]

Hello William,

Thank you for replying but I think what you are suggesting is a simple total by dimension or a subtotal row at the end of each material. What I am asking for is a Running Total or Accumulative Total in a new Column. So if there are 5 line items for the same material, each line will increment the total column by the previous lines value.

If you look at my example closely, the Available Qty column is that accumulative or running total but I cannot get it to reset with the change in dimension member value. I can do this in DWC with SQL with the code below but was hoping I could do this in the front end in SAC.

Example SQL code with Sum() Over() function.

SELECT

ROW_NUMBER() OVER(

PARTITION BY MATNR

Order by

"MATNR",

"SEQ_1",

"DATE",

"WERKS",

"MRP_ELEMENT",

"DEL_NUM",

"ORD_NUM"

) AS "Row_Number", --Creates a sequence for the output that resets with each new material number

SUM(REQ_QTY) OVER(

PARTITION BY MATNR

Order by

"MATNR",

"SEQ_1",

"DATE",

"WERKS",

"MRP_ELEMENT",

"DEL_NUM",

"ORD_NUM"

) AS "AVL_STK_CACL", -- creates a running total by material number in the same order as the sequence order

[William_Yu]

I don't see that option in SAC for the moment, no additional parameter allowed for keyword runinngsum() .