cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Stopping an RSUM after its last value

wallingfordce
9 - Travel Pro
9 - Travel Pro

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.

wallingfordce_0-1648057720496.png

 

4 REPLIES 4

Ophir_Buchman
Sisense Team Member
Sisense Team Member

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

Ophir_Buchman_0-1648108199614.png

If that's not the case, please:

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

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

wallingfordce_0-1648128187590.pngwallingfordce_1-1648128196251.pngwallingfordce_2-1648128202576.png

 

harikm007
13 - Data Warehouse
13 - Data Warehouse

@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

 

harikm007_0-1648131444197.png

 

harikm007_1-1648131452181.png

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

harikm007_0-1648131752008.png

 

-Hari

 

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
)

wallingfordce_0-1648133748745.png

 

Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Developers Group:

Product Feedback Forum:

Need additional support?:

Submit a Support Request

The Legal Stuff

Have a question about the Sisense Community?

Email [email protected]