avrage time of each category

[avishaiz]

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.

[MarcoWedel · Accepted solution]

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

[avishaiz]

thank you very much for the help i will try this and i will let you know how it went

[MarcoWedel]

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

[avishaiz]

for some reason i dont have the correct button

just mark as helpful

[MarcoWedel]

Don't edit your thread from your inbox.

Instead open your thread in a separate window, for this one e.g. using this link:

avrage time of each category

hope this helps

regards

Marco