Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
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
Maybe you could post a qvw with sample of your data
Good Idea Let me set some data up.
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