Last n consecutive results - Set Analysis

simonb2013 — Fri, 05 May 2017 08:43:49 GMT

So today's challenge is thus...

I have a set of activities (by category), each with a start time and duration.

I want to identify - where the last n actives (for each category) has increased in duration compared to the previous.

For illustration (n=2).

Category A durations : 100, 50, 75, 76 <-- Last 2 activities have increased in duration compared to the previous

Category A durations : 100, 50, 75, 60 <-- I am not interested in this as the last activity reduced duration compared to the previous

I started to play in a straight table to try and get a 'feel' for things.

The expression below sets each row to 1,2,3... for the number of consecutive increases, and resets to 0 if it meets a non-increase.

Dimensions are [Category] & [StartTime]

(

rangeMax( above( if(Above(Sum([RunTime])) - RunTime <1,1,0) ) ,0) // prev row increase

if(Above(Sum([RunTime])) - RunTime <1,1,0) // set to zero id this row is not an increase

)

if(Above(Sum([RunTime])) - RunTime <1,1,0) // this row increase

All that looks pretty in a straight table,

but I feel it needs to be done with SetAnalysis rather than 'if'.

I am struggling turning it something that only displays the final result for the last [StartTime] in a pivot or chart.

So in the below, I need the last n+1 rows to be involved in my calculation, but then only display the last row.

Any help/guidance appreciated !

topic Last n consecutive results - Set Analysis in QlikView