7 Replies Latest reply: Sep 25, 2013 3:04 PM by Oleg Troyansky RSS

    Avg Days in Que

    Kim Stroupe


      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

          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
            Kim Stroupe

            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
                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

              • Re: Avg Days in Que
                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

                • Re: Avg Days in Que
                  Clever Anjos

                  Maybe you could post a qvw with sample of your data