Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i am trying to create a chart that will show the average time on each station for the day week month and year of the products
i want to show the average time spent in:
assembly
waiting for testing
testing
waiting for packing
packing
i want to show it both in time (hours and minutes) and in percentage.
Hi,
maybe one solution might be:
(replacing negative durations (e.g. test start before assembly end) with zeros)
tabProduct:
LOAD *,
Interval([testing start]-[assembly end]) as [waiting for testing duration],
Interval([packing start]-[testing end]) as [waiting for packing duration];
LOAD *,
Interval([assembly end]-[assembly start]) as [assembly duration],
Interval([testing end]-[testing start]) as [testing duration],
Interval([packing end]-[packing start]) as [packing duration];
LOAD *,
DayName(Timestamp) as Date,
Timestamp([asembly start date]+[asembly start time]) as [assembly start],
Timestamp([asembly end date]+[assembly end time]) as [assembly end],
Timestamp([testing start date]+[testing start time]) as [testing start],
Timestamp([testing end date]+[testing end time]) as [testing end],
Timestamp([packing start date]+[packing start time]) as [packing start],
Timestamp([packing end date]+[packing end time]) as [packing end]
FROM [https://community.qlik.com/servlet/JiveServlet/download/1100408-239929/%D7%9E%D7%A2%D7%A7%D7%91%20%D...] (ooxml, embedded labels, table is [Form responses 1])
Where IsNum(serial);
tabCalendar:
LOAD *,
Day(Date) as Day,
WeekDay(Date) as WeekDay,
Week(Date) as Week,
WeekName(Date) as WeekName,
Month(Date) as Month,
MonthName(Date) as MonthName,
Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter,
QuarterName(Date) as QuarterName,
Year(Date) as Year,
WeekYear(Date) as WeekYear;
LOAD Date(MinDate+IterNo()-1) as Date
While MinDate+IterNo()-1 <= MaxDate;
LOAD Min(Date) as MinDate,
Max(Date) as MaxDate
Resident tabProduct;
tabDuration:
CrossTable (phase, duration)
LOAD serial,
[assembly duration] as assembly,
[waiting for testing duration] as [waiting for testing],
[testing duration] as testing,
[waiting for packing duration] as [waiting for packing],
[packing duration] as packing
Resident tabProduct;
hope this helps
regards
Marco
Hi,
maybe one solution might be:
(replacing negative durations (e.g. test start before assembly end) with zeros)
tabProduct:
LOAD *,
Interval([testing start]-[assembly end]) as [waiting for testing duration],
Interval([packing start]-[testing end]) as [waiting for packing duration];
LOAD *,
Interval([assembly end]-[assembly start]) as [assembly duration],
Interval([testing end]-[testing start]) as [testing duration],
Interval([packing end]-[packing start]) as [packing duration];
LOAD *,
DayName(Timestamp) as Date,
Timestamp([asembly start date]+[asembly start time]) as [assembly start],
Timestamp([asembly end date]+[assembly end time]) as [assembly end],
Timestamp([testing start date]+[testing start time]) as [testing start],
Timestamp([testing end date]+[testing end time]) as [testing end],
Timestamp([packing start date]+[packing start time]) as [packing start],
Timestamp([packing end date]+[packing end time]) as [packing end]
FROM [https://community.qlik.com/servlet/JiveServlet/download/1100408-239929/%D7%9E%D7%A2%D7%A7%D7%91%20%D...] (ooxml, embedded labels, table is [Form responses 1])
Where IsNum(serial);
tabCalendar:
LOAD *,
Day(Date) as Day,
WeekDay(Date) as WeekDay,
Week(Date) as Week,
WeekName(Date) as WeekName,
Month(Date) as Month,
MonthName(Date) as MonthName,
Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter,
QuarterName(Date) as QuarterName,
Year(Date) as Year,
WeekYear(Date) as WeekYear;
LOAD Date(MinDate+IterNo()-1) as Date
While MinDate+IterNo()-1 <= MaxDate;
LOAD Min(Date) as MinDate,
Max(Date) as MaxDate
Resident tabProduct;
tabDuration:
CrossTable (phase, duration)
LOAD serial,
[assembly duration] as assembly,
[waiting for testing duration] as [waiting for testing],
[testing duration] as testing,
[waiting for packing duration] as [waiting for packing],
[packing duration] as packing
Resident tabProduct;
hope this helps
regards
Marco
thank you very much for the help i will try this and i will let you know how it went
you're welcome.
Please close your thread once your question is answered:
Qlik Community Tip: Marking Replies as Correct or Helpful
There might be helpful and/or correct answers in your older threads as well.
hope this helps
regards
Marco
for some reason i dont have the correct button
just mark as helpful
Don't edit your thread from your inbox.
Instead open your thread in a separate window, for this one e.g. using this link:
hope this helps
regards
Marco