topic Re: Avg Days in Que in QlikView

Avg Days in Que

kstroupe — Fri, 20 Sep 2013 03:27:44 GMT

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.

Re: Avg Days in Que

Oleg_Troyansky — Mon, 23 Sep 2013 23:21:57 GMT

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

Re: Avg Days in Que

kstroupe — Wed, 25 Sep 2013 18:06:05 GMT

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

Re: Avg Days in Que

Clever_Anjos — Wed, 25 Sep 2013 18:17:15 GMT

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

Re: Avg Days in Que

kstroupe — Wed, 25 Sep 2013 18:23:43 GMT

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

Re: Avg Days in Que

Clever_Anjos — Wed, 25 Sep 2013 18:24:51 GMT

Maybe you could post a qvw with sample of your data

Re: Avg Days in Que

kstroupe — Wed, 25 Sep 2013 18:54:33 GMT

Good Idea Let me set some data up.

Re: Avg Days in Que

Oleg_Troyansky — Wed, 25 Sep 2013 19:04:26 GMT

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