topic Re: String Field on Maximum Date in Set Analysis in QlikView

String Field on Maximum Date in Set Analysis

sjhussain — Sun, 10 Dec 2017 17:30:21 GMT

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.

Re: String Field on Maximum Date in Set Analysis

swuehl — Sun, 10 Dec 2017 18:39:36 GMT

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.

Re: String Field on Maximum Date in Set Analysis

sergio0592 — Sun, 10 Dec 2017 18:46:14 GMT

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)

Re: String Field on Maximum Date in Set Analysis

luismadriz — Mon, 11 Dec 2017 05:42:41 GMT

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...?

Re: String Field on Maximum Date in Set Analysis

swuehl — Mon, 11 Dec 2017 18:48:19 GMT

Luis,

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

Re: String Field on Maximum Date in Set Analysis

luismadriz — Mon, 11 Dec 2017 22:41:06 GMT

Excellent, thanks Stefan!