0 Replies Latest reply: Nov 12, 2013 10:13 AM by Jonas Hertz RSS

    Count events up to certain date or time limit with set analysis

      My goal is to have a function calculating a sum (number of events) from two inputs: "Type" and "Time from vTimeNow". Field to be counted is called Count. The two inputs should select from the fields Type (using "=") and Time (using ">=").

       

      That is I wish to: "Sum the field count where Type=x and Time >= (vTimeNow - n hours)", where vTimeNow is the current time.

       

      The following set analysis will give me a result but the time is not based on a second input ($2):

       

      SET CountEvent = num( sum( {$<Type={$1}, Time={'>=14:00:00'}>} Count ), '#,##0' );

       

      I have tried all ways I can think of to add the second input but none has produced the correct result.

       

      For example I would hope this could have been successful but it did not produce the correct result (gave me a 0).

       

      LET vTimeNow = Timestamp('16:10:54', 'hh:mm:ss');

      SET CountEvent = num( sum( {$<Type={$1}, Time={'>=$(=Time( vTimeNow-$2/24 )))'} Count ), '#,##0' );

       

      What have I missed?