String Field on Maximum Date in Set Analysis

[sjhussain]

Hello All,

Hope all is well with everyone.

I am working on a dashboard which uses tickets history.  Ticket history is a transactional table consisting of ticket number, activity date, and status.

We are looking for the following KPIs:

• Number of tickets whose last status is Open

          Count({<MasterStatus = {'Open'}, dt_DATETIME = {'$(=max(dt_DATETIME))'}>}distinct TicketID)

          Not sure if this is giving the correct number? do I need to use aggr so that it can group by TicketID?

• I want to show it in Table also using Set Analysis

     Dimension: TicketID

     Measure: max(dt_DATETIME)

     Measure 2: How can I show the Status which corresponds to the max(dt_DATETIME)?

Will appreciate the help.

Thanks.

[swuehl]

The set modifier on dt_DATETIME using the dollar sign expansion will not consider each TicketID separately, it will just use the global max timestamp.

If you want to use set analysis, I would suggest to flag the latest records per TicketID in your script, then you can filter the latest records and select also / display the corresponding status.

For the flag, use maybe something like

Tickets:

LOAD TicketID,

           dt_DATETIME,

          ...

FROM ...;

LEFT JOIN (Tickets)

LOAD TicketID,

           MaxString(dt_DATETIME) as dt_DATETIME,

          1 as LatestRecord

RESIDENT Tickets

GROUP BY TicketID;

Now you can use {< LatestRecord = {1} >} in your expressions.

[sergio0592]

Hi,

- Number of tickets whose last status is Open

=sum(if(aggr(FirstSortedValue(MasterStatus,-dt_DATETIME),TicketID)='Open',1,0))

- Measure 2: How can I show the Status which corresponds to the max(dt_DATETIME)?

FirstSortedValue(Status,-Date)

[luismadriz]

Hi Stefan,

Why did you decide to use MaxString instead or Max? I read about MaxString on the Help but still don't get it.

Many thanks,

Luis

PS. I would have done what JB Renault did to get the results on this request but I imagine your solution is much better in terms of end-user performance for larger datasets...?

[swuehl]

Luis,

the dt_DATETIME field values should be created as duals, i.e. the should hold a numeric representation.

See also

Get the Dates Right

Why don’t my dates work?

Then it shouldn't really matter if you use Max() or Maxstring() in my sample script.

(Note that Maxstring applied on duals also use the numeric value for sorting, see

A more comprehensive HELP on Maxstring() / Minstring() than the HELP file

)

I've suggested to create a flag in the script for the latest records per ticket, because this allows to use set analysis to filter the relevant records.

Of course, you can also use FirstsortedValue() as suggested above, but set analysis should perform much better on larger data sets.

[luismadriz]

Excellent, thanks Stefan!