Stopping an RSUM after its last value

[wallingfordce]

When using RSUM over a category, and when the RSUM has underlying values only for some early periods of time, and not later periods, it sums in the NULLs from those future periods showing values extending into the future. I do want the RSUM to be continuous in cases where there were NULLs in the middle of the trend, but in cases when it's the "end of the line" (e.g. all dates are future and have no sales), I'd like the line to end.

[Ophir_Buchman]

Hi @wallingfordce 

What you're seeing here is not the default behavior of Sisense as NULL values do not automatically represent zeros. Please try the following:

1. Change the chart type to "Pivot" and check if the "Missing Data" values show as "0" or empty
2. If they show up as "0" - Uncheck the "Display Missing Values as Zeros" checkbox

If that's not the case, please:

1. Share the version of Sisense you're using
2. Shar a screenshot of the "Pivot" chart

[wallingfordce]

Thanks for this. I'm looking at W2021.4. I was incorrect about my data and did at have zeros in there instead of NULL. I updated the formula to ensure that there are NULL instead of zero. I unchecked display missing as zero. Seems that regardless of 0/NULL and regardless of checked status of display missing as zero, it renders the 0s in the RSUM.

Seems like I should go the support ticket route. 
Thanks

[harikm007]

@wallingfordce ,

Please try this formula if that gives you expected result. (use column with 0 instead of NULL)

case when [Total this year] = 0 then null else Rsum([Total this year]) end

If you are using column having NULL instead of 0, try this:

-Hari

[wallingfordce]

Thanks Hari, I have great appreciation for your posts.

In this "case," it would need to be more intricate. There could be valid zeros in the series. This is the formula I was using, but it's a little expensive and I was hoping to simplify...

• if the rsum for the selected FY at this point in the curve is < the total for the widget for the selected FY , then we're not at the end of the curve
• if the rsum for the selected FY at this point in the curve is = the total for the widget for the selected FY , then we might be at the last point or past the end of the curve...

• if the ticket count in at this point is > 0 then we're at the last point on the curve
• else we're past the last point on the curve

• if we not past the end of the curve, show the rsum value
• else show null

Displayed as the "Alt" values in the screenshot.

IF (
RSUM( ( [Total Count] , [Fiscal Year] ) ) < ( [Total Count] , [Fiscal Year] , ALL( [Calendar Month Name]) )
  OR (
  RSUM( ( [Total Count] , [Fiscal Year] ) ) = ( [Total Count] , [Fiscal Year] , ALL( [Calendar Month Name]) )
  AND
  ( [Total Count] , [Fiscal Year] ) > 0
    )
  , RSUM( ( [Total Count] , [Fiscal Year] ) )
  , NULL
)