8 Replies Latest reply: Sep 19, 2017 12:08 PM by Florent Garel RSS

    Set analysis - only retrieve the last line for a reference

    Florent Garel

      Hello,

       

      I have set up a chart that counts a number of tickets in support entries per day, per week, per month (based on a single criteria) from a complex table containing all entries, knowing that a ticket can enter (and exit) several times over the same period.

       

        My source table has this form:

       

       

      Comment

      Row id

      Référence

      date

      In/out flag

      status

      status_flag

      group

      group_flag

      Creation of the ticket

      10102

      1025639

      09/11/2017 14:00:00

      I

      open

      1

      Grp1

      1

      Change of group

      10256

      1025639

      09/11/2017 15:00:00

      O

      open

      0

      Grp1

      1

       

      10365

      1025639

      09/11/2017 15:00:00

      I

      open

      0

      Grp 2

      1

      Change of status

      10459

      1025639

      09/12/2017 15:10:00

      O

      open

      1

      Grp 2

      0

       

      10468

      1025639

      09/12/2017 15:10:00

      I

      InProgress

      1

      Grp 2

      0

      Change of group

      10596

      1025639

      09/13/2017 15:30:00

      O

      InProgress

      0

      Grp 2

      1

       

      10599

      1025639

      09/13/2017 15:30:00

      I

      InProgress

      0

      Grp 3

      1

      Change of status

      10601

      1025639

      09/14/2017 09:30:00

      O

      Resolved

      1

      Grp 3

      0

       

      10602

      1025639

      09/14/2017 09:30:00

      I

      Resolved

      1

      Grp 3

      0

      Change of group

      10605

      1025639

      09/14/2017 12:30:00

      O

      Resolved

      0

      Grp 3

      1


      10607

      1025639

      09/14/2017 12:30:00

      I

      Resolved

      0

      Grp 21

      ...

       

      Note: the flag lets you know on which field the entry is to count(group, status, application, etc.)

       

      I try to count the number of entries per group

      My expression is 'rather' simple:

      = count {{in_out = {'E'}, group = p (group_name)>} DISTINCT if (group_flag, reference)

       

      that works well. the Distinct does count me only one ticket even if it has been assigned twice in September to the Gpe2.

       

      The problem happens when I want to show status when receiving tickets.

      There it counts 1 for the status Open and 1 for the status Resolved.

      I only want to count the first (or last) entry I have

       

      So get the min (date) of my entries to have only one line (and no need to make a distinct anymore).

       

      Of course, I thought of the solution in the LOAD, but it's a dynamic screen and I can not pre-calculate everything.

       

      Thanks in advance for your help.

       

      Regards

       

      FG