3 Replies Latest reply: Nov 17, 2016 5:46 AM by Sunny Talwar RSS

    Graph Fields after and before a firstsortedvalue date

    JUAN villalva

      Hi

       

      My data inputs is like this:

       

      LOAD

        device,

          Date(fec_recep, 'MM/DD/YYYY hh:mm:ss') as Fecha_recep,

          event_name,

          List,

          SubField(List,'DG') as CODE,

        Date(fec_recep, 'MM/DD/YYYY hh:mm:ss') as fechaCODE

      FROM [lib://MSG/*.qvd]

      (qvd);

       

      Those List is a concatenated list of CODE's and came only in an specific event_name, not all event_names has it. That is why I made a subfield in order to split the list of CODE's

       

      Now I have this graph of the CODE's for an specific device:

       

      CODE_graph1.PNG

       

      in the previous graph:

      Dimention: CODE

      Measure: Count(CODE)

       

      Now I want to graph the CODE's that were generated (fechaCODE) before the first date of an specific event name of every device:

       

      I tried:

      Dimention: CODE

      Measure: COUNT(AGGR(if(fechaCODE< FirstSortedValue(AGGR({$<event_name={"*SpecificEvent*"}>} Fecha_recep,device),Fecha_recep,1),CODE),CODE))

       

      Then I tried to make the same graph but with those CODE's created just before FirstSortedValue(AGGR({$<event_name={"*SpecificEvent*"}>} Fecha_recep,device),Fecha_recep,1).

       

      If I make a table of CODE and FirstSortedValue(AGGR({$<event_name={"*SpecificEvent*"}>} Fecha_recep,device),Fecha_recep,1), IT WORKS:

       

      date1.PNG

      And I want just to count the CODE's in order to test the Measure and does not works:

       

      Count is: count(aggr(If(fechaCODE< firstsortedvalue({$<event_name={"*Specific Event*"}>} Fecha_recep , Fecha_recep,1), fechaCODE),device,CODE))

       

      date2.PNG

       

      Here is an input data example:

       

       

      deviceevent_nameListfec_recep
      serial1Event1DGXDGYDGZ01-01-16
      serial1Event202-01-16
      serial1Event203-01-16
      serial1Event204-01-16
      serial1Event1DGXDGZ05-01-16
      serial1Event1DGXDGZ06-01-16
      serial2Event1DGXDGZ02-01-16
      serial2Event1DGXDGZDGW03-01-16
      serial2Event1DGXDGZDGW04-01-16
      serial2Event205-01-16
      serial2Event206-01-16
      serial3Event1DGX05-01-16
      serial3Event1DGX06-01-16
      serial3Event1DGX07-01-16
      serial3Event1DGX08-01-16
      serial3Event1DGX09-01-16
      serial4Event217-01-16
      serial4Event218-01-16
      serial4Event219-01-16
      serial4Event1DGZ20-01-16
      serial4Event1DGX21-01-16

       

      I load the data like this because "list" has the Codes concatenated:

       

      LOAD

          device,

          event_name,

          List,

          Date(fec_recep, 'MM/DD/YYYY hh:mm:ss') as Fecha_recep,

          SubField(List,'DG') as CODE,

          Date(fec_recep, 'MM/DD/YYYY hh:mm:ss') as fechaCODE

        

      FROM [lib://test/data.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

       

      I could make this graph  with

      Dimention: CODE

      Measurement: COUNT(CODE)

      CODES.PNG

       

      now I want to count only the CODEs that were generated before the oldest Event 2 date,

       

      The graph I need:

       

      I tried:CODES2.PNG

      Dimention: CODE

      Measure: COUNT(AGGR(if(fechaCODE< FirstSortedValue(AGGR({$<event_name={"Event2"}>} Fecha_recep,device),Fecha_recep,1),CODE),CODE))

       

      Message was edited by: JUAN villalva