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

[#1]

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

[mazacini]

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]

[mazacini]

Duplicated response - deleted

[mazacini]

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

[pover · Accepted solution]

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.

[mazacini]

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

[#7]

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

[pover]

Assuming you are using an expression like count(if(state='closed',theDate)) try concatenating a table that contains every month so that QlikView knows the 12 months in the year, but with null information in "theDate" or "state" then in the presentation tab of the pivot table, untick the options to suppress zero values and suppress null or missing values.

Regards.

[#9]

Thanks for the pointer. I originally used a pivot table, but decided on a straight table with expressions for each of the milestones, as this would give the totals too. I had to tweak the supress zero and suppress null/missing settings, but eventually managed to get what was neeeded:

Thanks very much for your help and advice.

Adrian