12 Replies Latest reply: Feb 16, 2016 7:40 AM by Roland Loykens RSS

    Set Analysis for Prior Periods Cumulative Sum with Non Contiguous Period Selections

    Graeme Smith

      Hi,

       

      I am working with some data whereby I need to calculate and display cumulative balances over time.  To maintain maximum flexibility, I am hoping to achieve this via set analysis and chart expressions (as opposed to precalculating summary tables and or running balances in the load script, as this brings with it other complexity, and reduces overall fexibility) if at all possible. 

       

      I have put together a grossly simplified example, but I am hoping it should be enough to demonstrate the problem.  The sample contains data for a year, where there are values for each month.  These are displayed in a pivot table where the periods are pivoted across the top.  There are two expressions on the pivot to display the current month value, and the cumulative value (which I have calculated with rangesum.

       

      All Periods.png

       

      The next part of the puzzle is providing users the capability to filter specific periods off the chart, but whilst maintaining the appropriate running balances.  If a user selects specific months (E.g. Mar, May, Dec) they should see the chart filtered for those months.  The expression values from the chart above should essentially remain the same, but only the selected months should be displayed (see below for example of what I am aiming for).

      Filtered Prior Periods.png

       

      I managed to get this working for a single contiguous range of dates being selected using a combination of RangeSum and Set Analysis (see below), but now need to take it to the next level to support non contiguous date range selections.

       

      =RangeSum(before(sum(Value),0,ColumnNo())) 
      +
      alt(
        sum(DISTINCT
        Aggr(NODISTINCT
        sum (
        {1 
        <PeriodNumeric={"< $(=Min(PeriodNumeric))"}>
        } 
        TOTAL <Region, SubRegion>
        Value)
        ,Region, SubRegion)
        )
      ,0)
      
      

       

      I know this is achievable by pre calculating the data in the load script, but I am trying to avoid this to maintian maximum flexibility in the UI to support ad hoc requests.  Any ideas or suggestions on how I might approach this using set analysis and or expressions?  Hopefully I'm missing something really simple...  Thanks in advance for your help.

       

      Cheers,

       

      Graeme