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...
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.
If you wanted to generate weekly rows for each defect, you could do it using the WHILE Clause - something like this:
week_beg_dt + 7*(IterNo() - 1) as DefectWeek,
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.