topic Re: How can I make a scatter plot with multiple dimensions to display status, time and crew? in QlikView

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

Wed, 25 Nov 2020 16:16:04 GMT

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.

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

MarcoWedel — Mon, 30 Jan 2017 23:39:44 GMT

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

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

Tue, 31 Jan 2017 13:41:29 GMT

Hi Marco,

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

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

MarcoWedel — Tue, 31 Jan 2017 21:43:53 GMT

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

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

MarcoWedel — Tue, 31 Jan 2017 21:45:37 GMT

please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco

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

Wed, 22 Mar 2017 22:28:37 GMT

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)"