How can I make a scatter plot with multiple dimensions to display status, time and crew?

[#1]

Hello,

I'm new to Qlik Sense and hoping to make a visual that displays the time various "Crews" are statusing throughout the day. The goal is to create a visual that shows what time each crew is statusing work at. We want to be able to see that crews are clicking different statuses throughout the day, and not only at one time of the day. I've attached a word document that is the general idea of the visual that I want to create (but likely in a scatter plot or line graph) and a sample of the raw data.

I would like each status: i.e. Hold, Start, Enroute to be represented by a different shape or color and would like to be able to see it by crew, date and time.

Is there a way I can make this visual in Qlik? I've already used maketime formula to extract the time from the field "Changed Time Stamp", but am still not able to plot the Mobile Object Status or time.

[MarcoWedel · Accepted solution]

Hi,

maybe one solution might be a pivot table:

mapStatus:
Mapping
LOAD * INLINE [
    Mobile Object Status, StatusSymbol
    COMPLETED, ⏹
    ENROUTE, ⏺
    HOLD, *
    STARTED, ▲
];

table1:
LOAD *,
    DayName([Effective Time Stamp]) as Date,
    Dual(Date([Effective Time Stamp],'WWWW'),WeekDay([Effective Time Stamp])) as WeekDay,
    Time(Frac([Effective Time Stamp])) as Time,
    Time(Floor(Frac([Effective Time Stamp]),'00:30'),'hh:mm') as TimeHalfHour,
    Hour([Effective Time Stamp]) as Hour;
LOAD Crew,
    [Op Center],
    [Work Order],
    [Mobile Object Status],
    ApplyMap('mapStatus', [Mobile Object Status]) as StatusSymbol,
    Timestamp(Timestamp#([Effective Time Stamp],'YYYYMMDDhhmmss')) as [Effective Time Stamp]
FROM [https://community.qlik.com/servlet/JiveServlet/download/1202793-263130/Sample%20Data.xlsx] (ooxml, embedded labels, table is [WM - User Status Time]);

hope this helps

regards

Marco

[#3]

Hi Marco,

Thanks a lot! The only question is that I've been working in Qlik Sense - will this same script work in that?

[MarcoWedel]

Hi,

same example using Sense:

mapStatus:
Mapping
LOAD * INLINE [
    Mobile Object Status, StatusSymbol
    COMPLETED, ■
    ENROUTE, ●
    HOLD, *
    STARTED, ▲
];

table1:
LOAD *,
    DayName([Effective Time Stamp]) as Date,
    Dual(Date([Effective Time Stamp],'WWWW'),WeekDay([Effective Time Stamp])) as WeekDay,
    Time(Frac([Effective Time Stamp])) as Time,
    Time(Floor(Frac([Effective Time Stamp]),'00:30'),'hh:mm') as TimeHalfHour,
    Hour([Effective Time Stamp]) as Hour;
LOAD Crew,
    [Op Center],
    [Work Order],
    [Mobile Object Status],
    ApplyMap('mapStatus', [Mobile Object Status]) as StatusSymbol,
    Timestamp(Timestamp#([Effective Time Stamp],'YYYYMMDDhhmmss')) as [Effective Time Stamp]
FROM [lib://SampleData] (ooxml, embedded labels, table is [WM - User Status Time]);

hope this helps

regards

Marco

[MarcoWedel]

please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco

[#6]

Hi Marco,

Is there a way I can add the last 5 digits of the Work Order beside the Status Symbol?

For example "E(98546), S(98546), C(98546)"