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

[#1]

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.

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).

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

[sgrice]

This should do it..

Expression:-

aggr( NODISTINCT rangesum(above(sum ({1}DISTINCT Value),0,50)),Region, SubRegion,PeriodNumeric)

All you needed is a rangesum above done in an aggr that is not effected by selections or dimensions and then for the same Region, SubRegion,PeriodNumeric to be wired backup by qlikview.

Regards

Steven

[#3]

hi Graeme,

I want to use your example on the below example:

on my data set I have a field 'class' the holds two outcomes 'legal' and 'not legal'

I want to sum the total amount when:

- if the previous month, the accountNumber's class was 'not legal'

- and current month, the accountNumber's class is 'legal'

meaning I want to sum total amount for only the accountNumbers that have transitioned from not legal (in the previous month) to legal (current month)

Your assistance will be highly appreciated

[roland74]

Thanks Graeme, very useful!