Forum Discussion

wallingfordce's avatar
wallingfordce
Data Pipeline
03-23-2022

Stopping an RSUM after its last value

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.

 

dashboards & reporting

4 Replies

Replies have been turned off for this discussion
  • Ophir_Buchman's avatar
    Ophir_Buchman
    Data Integration
    03-24-2022

    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's avatar
      wallingfordce
      Data Pipeline
      03-24-2022

      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's avatar
    harikm007
    Data Warehouse
    03-24-2022

    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's avatar
      wallingfordce
      Data Pipeline
      03-24-2022

      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
)