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.
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
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
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.
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
month(insrec) as mon,
year(insrec) as yar,
insrec as theDate,
'Received' as state
where insrec <> '';
month(date_closed) as mon,
year(date_closed) as yar,
date_closed as theDate,
'Closed' as state
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
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,
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.
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.