Avg Days in Que

[kstroupe]

Hello,

I am very new to QlikView but I hoping someone has figured this out already.  I need to count the number of defects for a week. Get the Sum of the days the defect was open and then take the count of the ttl defects for the week / sum of the days the defect was open.  Put this on a graph by week and figure out the Avg Days the defects were open. Any idea how to get this started? 

Here is an example of the data.  I have two ways to possibly do this.

defect 1 was created 1/2/13 and closed on 2/16/13 therefore it must be counted every week from 1/2 until it closes.

[Oleg_Troyansky]

Kim,

From your explanation, it's not too clear what is the required formula: "count of the ttl defects for the week / sum of the days the defect was open" is not the same as "the Avg Days the defects were open".

The solution could be different depending on how do you want to count defects vs. weeks. For example, if the defect was open on 1/2/13 and it was open for 6 weeks - would you like to count a single defect in the week it was open and the duration of 6 weeks, or would you like to count the event in each week while it was open (6 times in this example), with the duration of 6 weeks?

It would be much simpler if you associated the defect with the week it was either open or closed. If you had to count it in every week while it's open, the solutions are quite complex, unless your dataset is very small and you can afford using IF statements...

I'd be happy to help you if you'd like to clarify your requirement a little bit...

Oleg Troyansky

www.naturalsynergies.com

[kstroupe]

Oleg,

Thank you for your response.  The answer is the later of your example.  Cound the defect for each week which would be open 6 times.  I think I'm going to just write the SQL and bring it into QlikView rather then trying to have QlikView App  bucket it.  My SQL will then give me the following

week_beg_dt     week_end_dt  qty

1/6/2013                1/12/2013     1

1/13/2013               1/19/2013     1

and so on until the week it's closed.  then I will just use Week_begin_dt in my graph.

Unless you know of a way that QlikView can do this.

Thank you

Kim

[Clever_Anjos]

I think you can do

1) Create a disconnect table for each week and uses it as dimension

LOAD

  date(WeekStart(Today() - RecNo())) as Week

autogenerate 50; // 50 weeks

2) use Set Analysis to count defects

[kstroupe]

Thank you for your help.  I have a disconnect table but I'm not real familiar with Set Analysis.  I will look it up and see what I can come up with.  Thanks

[Clever_Anjos]

Maybe you could post a qvw with sample of your data

[kstroupe]

Good Idea Let me set some data up.

[Oleg_Troyansky]

If you wanted to generate weekly rows for each defect, you could do it using the WHILE Clause - something like this:

LOAD

Defect,

...

week_beg_dt + 7*(IterNo() - 1) as DefectWeek,

...

resident ...

WHILE week_beg_dt + 7*(IterNo() - 1) <= week_end_dt

;

IterNo() is the function that returns the iteration number, beginning with 1.

WHILE clause will cause QlikView to create multiple rows as long as the condition is true

This will obviously enlarge your table (I hope your data volumes allow that), but your on-line performance should improve and you won't need to use any complex Set Analysis. 

cheers,

Oleg Troyansky