Hi, Im very new to QlikView.
Im attempting to create a summary of information, but is not sure how the best is to go about it. Im working on a ticket system, where the current problem is that I want to see how many new tickets are opend every month of the year, how many is closed, and how many is carried over to the next month (the difference between open and closed).
The table Im attempting to create should contain the following information
This should be used to create a bar diagram with caried over as bars, and lines for open and closed, where the months are at the x-axis and the number of tickets are on the Y-axis.
How do I accomplish this. I would be very greatful for explanation on why you suggest what you do.
Thank you in advance for your help and your time.
I have the following tables (and a few more, though I do not think they are relevant)
|JOB_TICKET_ID, --> KEY|
|Floor(REPORT_DATE) as Date,|
|IF(STATUS_TYPE_ID = 5, LAST_STATUS_UPDATE_TIME,|
|IF(STATUS_TYPE_ID=3, CLOSE_DATE, )) as Fixdate,|
|Date(Floor(Fixdate),'YYYY-MM') as Fixmonth,|
|1 as TicketRecordCounter,|
|(TimeStamp(REPORT_DATE, 'YYYY-MM-DD hh:mm')) as Ticket_Report_Date,|
|(Date(Date, 'YYYY-MM')) as Ticket_Open_YearMonth,|
|(Date(CLOSE_DATE, 'YYYY-MM')) as Ticket_Close_YearMonth,|
|if(CLOSE_DATE <> LAST_STATUS_UPDATE_TIME, LAST_STATUS_UPDATE_TIME, CLOSE_DATE) as Ticket_Last_Updated,|
|if(CLOSE_DATE <> LAST_STATUS_UPDATE_TIME,|
|(Interval(LAST_STATUS_UPDATE_TIME - REPORT_DATE, 'mm')),|
|(Interval(CLOSE_DATE - REPORT_DATE,'mm'))|
|) as Ticket_Time_as_of_last_update_test2,|
|if(Today()-REPORT_DATE <= 90, 'less than 3 month',|
|if(Today()-REPORT_DATE >= 365, 'More than 1 year', 'More than 3 month')) as Group_Age|
|Date(TempDate) as Date,|
|Year(TempDate) as Year,|
|Month(TempDate) as Month,|
|Day(TempDate) as Day,|
|Week(TempDate) as Week,|
|Weekday(TempDate) as Weekday,|
|'Q' & Ceil(Month(TempDate)/3) as Quarter,|
|Date(MonthStart(TempDate), 'YYYY-MM') as YearMonth,|
|Year(TempDate) & '-' & Week(TempDate) as YearWeek|