16 Replies Latest reply: Aug 26, 2012 5:02 PM by Sebastian Pereira RSS

    Set analysis Question

      Hello everyone,

       

      I have a table like this:

       

      DateTimeStatusTap_Id
      01/01/2012 14:00open1
      01/01/2012 15:00close1
      02/01/2012 14:00open1
      02/01/2012 4:30close1
      02/01/2012 4:45open1
      03/01/2012 20:00close1
      03/01/2012 20:00open1

       

      I want to build a simple chart that will present for each day and tap the last status  - open or close (the biggest datetime for each day)

       

      Thanks in advance.

        • Re: Set analysis Question

          Hi Matan,

           

          Presuming you don't wish to edit the script (which would be the easiest and most efficient way to do this) this is how I would construct my chart:

           

          Dimension: Add calculated dimension of

          =date(floor(Datetime))

          this will remove the time from the timestamp and group your items as just a date (Date() on it's own migiht work, but I haven't tested it as a dimension)

           

          Expression "latest Status" : =firstsortedvalue(Status, -DateTime)

           

          Firstsorted value takes the first status when you order by "-Datetime" IE the "last" in the day.

           

          Regards,

           

          Erica

          • Re: Set analysis Question

            Hi, I've come to the conclusion that you cant achieve this with set analysis, the reason is below:

             

            The reason is, that when you define a set in set analysis it defines the set as if you were making selections in all the data. It doesn''t take into account the dimension or divide up the data by dimension.

             

            So if you include an expression in the set analysis, this is performed first over the full set of data before being applied to each element in the dimension.

             

            so in the example above if you were to reference $(=max([chosen_field]) in the set analysis this would work out the max(chosen_field) for all the data before applying that static max to the dimension. So there is no immediately easy way to use set analysis differently dependant on what was in the dimension.

             

            Hope this helps clarify things!

             

            Erica

              • Re: Set analysis Question
                Sebastian Pereira

                Dear Matan, I like the people that never gives up!!

                 

                In first place, with a table with "date" as dimention (if you don't have the "date" field, you could create with a calculated dimention like =Left(date(DateTime),10)), the expression that gives to you the last state is:

                 

                =FirstSortedValue(State, -DateTime)

                 

                But, for make this with SA, you need to tell to the expression witch values are available. These available values will be only the max for each day. The expression would be:

                 

                Only({<DateTime={$(vMaxTime)}>}Status)

                 

                Because of sintaxis of SA, you cant use search query inside another search query, and to make that you need to make a variable. In this case, it would be:

                 

                ='"*'&Concat(left(Timestamp(Aggr(max(DateTime),date)),16),'","*')&'"'

                 

                This variable creates a search string, with the values of the max for each day. From inside to outside, Aggr(Max(DateTime), date) gives you the max values aggregated by date. Then, with TimeStamp, you transform it to a timestamp string. I used Left to pick out the seconds, because if you use the [Format] parameter of TimeStamp, you could give round problems, and have a difference of minutes, creating invalid timestamps. Note that if you have hours less than 10, you should use left(...., 15). I recomend that you format these field to HH:MM:SS, or HH:MM and don't use Left.

                 

                Then, the Concat creates the complete string with all the max values.

                 

                Im agree with you, IT IS POSSIBLE!!