8 Replies Latest reply: Jan 11, 2011 10:34 AM by adrianjthomson RSS

    Can I generate a summary matrix of vol of cases at milestone per month

    adrianjthomson

      Hello

      We have a requirement to provide a summary of the number of cases at a particular milestone for each month in the selected year. The lifetime of a case may be that its opened in January, gone to trial in April, Settled in June and Closed in July.

      For example, the data may be like the following:

      case_id, opened, trial, settled, closed

      1234,12/01/2010,null,13/04/2010,15/04/2010

      3212,01/02/2010,03/03/2010,25/03/2010,12/04/2010

      The aim is to have a table chart in qlikview like:

      month opened trial settled closed

      Jan 1 0 0 0

      Feb 1 0 0 0

      Mar 0 1 1 0

      Apr 0 0 1 2

      ...

      Total 2 1 2 2

      The easy option is to generate a summary table in the load script (or at the database) that contains the summary matrix as appropriate, and is easy to display in qv.

      However, I was wondering if it was possible to get the totals via expressions - if we knew the year selected (this would be a single selection list box) and the current month (the month of the opened date would be the dimension of the table/chart) for the cell, then we could have an expression like sum(if(settleddate_is_in_month_and_year, 1, 0))

      The more I think about it, it seems like qv is not really set up for this? Unless I'm missing something whereby it can be done in qv relatively easily? Its sort of bugging me as I'm making the transition to qv from developing client extranets/dashboards with Adobe Flex, so in some ways I still have my programming hat on, so might not be looking at the problem in the correct way.

      Apologies if its not too clear, let me know what bits aren't and I'll try to expand on them.

      Regards

      Adrian

        • Can I generate a summary matrix of vol of cases at milestone per month
          Joe Kirwan

          Hi

          I' don't have any programming, so apologies if this is a dumb suggestion from a progrmmatical pov.

          I think this could be done by Pivot. You might end up using the data differently to arrive at the same result, but so what.

          The one thing I could not do is summarise by month - but I suspect that is a simple fix to the LOAD for the relevant field.

          My basic starting point is that every case will have both an ID and Opening Date.

          In the Pivot, I use the Opening Date as my dimension. Then I have a COUNT expression each for CASEID, Trial, Settled and Closed.

          My model has blank fields where no dates for relevant events. So if e.g. no trial, then field is blank and not included in count.

          As I say, the only thing to figure out is how to get Opened dates to group by month.

          BTW, if this is a runner, and you figure the date thing out, you might let me know.

          Here is what my Pivot looks like, from the attached xlsx file.

          Rgds

          Joe

           

           

          [View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/674/4760.Forum-384772.xlsx:550:0]

          • Can I generate a summary matrix of vol of cases at milestone per month
            Joe Kirwan

            Hi

            I changed the LOAD script as follows:

             

             

            Month (Opened) as Opened

             

            The Pivot now looks like this:



             

            Now I reckon all you need to figure out is how to introduce the year element so that the "Opened" column is in format mmm-yy, rather than just mmm as above.

            Joe

             

              • Can I generate a summary matrix of vol of cases at milestone per month
                Karl Pover

                Joe,

                Your report is fine if the question is

                Of the cases opened in January, how many have gone to trial, been settled and/or been closed?

                However Adrian might be asking is

                In January how many cases were opened, went to trial, settled and/or closed?

                So month has to have no relation to any of the dates. You can do this using a date island table (search in the community for related posts) and then you can do it by a count(if) expression.

                However, the best way is to reorganize the table as follows:

                CaseID, Date, Action
                1234, 12/01/2010, Opened
                1234, 13/04/2010, Settled
                1234, 15/04/2010, Closed
                etc....

                You can do this in the script with a crosstable function and then all actions share the same date column and the report is pretty straight forward.

                PS. Use MonthName(Opened) to introduce Month/Year

                Regards.

                  • Can I generate a summary matrix of vol of cases at milestone per month
                    Joe Kirwan

                    Hi Karl

                    Thank you for yorr tip on the MonthName function.

                    If my interpretation of the question is correct, then your solution completes mine very nicely.

                    You are correct in that the post is ambiguous. I am new to Qlikview and will work through your solution (after Christmas!). I was guided by the fact that Adrian referred to Opened as the dimension. I think your solution suggests that Month would be the dimension?

                    Anyway, for me your postimg is adding to my knowledge, so thank you.

                    Joe

                    • Can I generate a summary matrix of vol of cases at milestone per month
                      adrianjthomson

                      Thanks for all the replies, I have nearly sorted it. The source table has over 100 columns - its an extract from a law case system with all the details for the cases, so decided to create another table to contain the case_id, date, state, month and year as follows

                      TheDates:

                      load

                      case_id,

                      month(insrec) as mon,

                      year(insrec) as yar,

                      insrec as theDate,

                      'Received' as state

                      from mycases.xls

                      where insrec <> '';

                       

                      concatenate (TheDates)

                      load

                      case_id,

                      month(date_closed) as mon,

                      year(date_closed) as yar,

                      date_closed as theDate,

                      'Closed' as state

                      from mycases.xls

                      where date_closed <> '';

                       

                      and so on for the other states

                       

                      I can then create a pivot chart with mon and state as dimensions, with count(case_id) as the expression, and moving the state dimension to the top so it looks like

                      Received Closed Settled

                      Jan 5 1 0

                      Feb 3 0 9

                       

                      etc, which is exactly what I wanted to achieve.

                      The only problem I have come across is that there are quiet months, when there are no cases received, closed or settled, so these don't appear in the pivot chart - I get something like

                      Jan

                      Feb

                      Mar

                      May

                      Aug

                      Sep

                      Nov

                       

                      So is there any simple way of ensuring that all the months are present in the pivot chart, even if there is no data for a month? Obv the pivot chart is just using mon's data (mon is the primary dimension) and doesn't know about the fact that there are 12 months in a year. It may be something completely obvious that I'm missing.

                      Happy new year,

                      Adrian