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 !