0 Replies Latest reply: Jun 30, 2017 4:38 PM by Will Snipes RSS

    RangeSum has nulls in cumulative trend

    Will Snipes

      Hello Qlik Community,

       

      I'm trying to create a pivot table with a Year-Month range of the last 12 months in columns and products as rows.  What I want is a cumulative sum across the months in the pivot table and I get that with the following formula:

       

      RangeSum(Before(Sum({<WorkitemStatus={'','Review','Ready','Analysis','In Progress','In Testing','Done'}>}(FactWorkitemHistoryCount)),0,ColumnNo()))

       

      However, because all products do have data for each month I'm getting nulls in months with no data.  What I want to see the the same value from the previous month when there is no change data for the current month.

       

      The data source is a fact table with two rows for each change timestamp.  One to subtract the previous values and another to add the new values.  A sample of the data is attached for explanation.  The entire data model would be necessary to replicate the report.

       

      Thanks for your help.