6 Replies Latest reply on Dec 16, 2016 8:11 AM by Kwesi Enninful

# Calculate a running sum on rows if current row is 1 else default to 0

Hi,

I am trying to recreate attached excel view (row 4 colored in Blue) in tableau without using Table Calc.

Basically, I would like do a running sum on row 4 if the current cell in row 3 is 1 or default to 0 if the current cell in row 3 is 0. Hence, count consecutive weeks if cell is 1 else 0 which I have replicated in attached excel and would like to do same in tableau without using table calculation.

I don't want to use table calc since I want to apply the fixed or include function to aggregate the data further.

• ###### 1. Re: Calculate a running sum on rows if current row is 1 else default to 0

Hi Kwesi,

Find my approach as reference below and stored in attached workbook version 9.3

Running sum adj.: if attr([Value])<=0 then 0 else  sum([Value])+PREVIOUS_VALUE(0) END

• ###### 2. Re: Calculate a running sum on rows if current row is 1 else default to 0

Hi Norbert, Thanks for responding. The example you provided uses table calculation. I am looking for an approach that does not use table calculation.

Thanks,

Kwesi

• ###### 3. Re: Calculate a running sum on rows if current row is 1 else default to 0

Hi Kwesi,

Nor sure what you meant "does not use table calculation"....How would you like to achieve this without using a "Calculation field" or table calculation?

• ###### 4. Re: Calculate a running sum on rows if current row is 1 else default to 0

Hi Norbert,

I would like to use a calculation field instead of table calculation. Basically, I want to aggregate the data further and roll it up to a higher level using the level of detail (LOD) functions in tableau such as Fixed or Include function. Table calculation does not support level of detail functions and that is why I'm looking for another approach which supports LOD.

Thanks,

Kwesi

• ###### 5. Re: Calculate a running sum on rows if current row is 1 else default to 0

Hoi Kwesi,

Could you share the workbook with the complete scenario?

• ###### 6. Re: Calculate a running sum on rows if current row is 1 else default to 0

Hi Norbert,

Please see attached. On sheet 2, I would like to count department id which have a running sum of at least 4 and over without showing  department id. I can accomplish this if I use level of detail function in tableau but the challenge is that level of details is not compatible with table calculation. That is why I would like to perform the rolling sum using a different function which is not table calculation.