topic Re: For each loop - creating a summary of tickets pr month in QlikView

For each loop - creating a summary of tickets pr month

Thu, 11 Jul 2013 09:30:58 GMT

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

YearMonth

Tickets_Open

Tickets_Closed

Tickets_Carried_Over.

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.

Background info:

I have the following tables (and a few more, though I do not think they are relevant)

Tickets:

	ASSIGNED_TECH_ID,
	CLIENT_CREATOR_ID,
	CLIENT_EMAIL,
	CLIENT_ID,
	CLIENT_NAME,
	CLOSE_DATE,
	CLOSE_YEARMONTH,
	FIRST_RESPONSE_DATE,
	Fixtime_hours,
	JOB_TICKET_ID, --> KEY
	JOB_TIME,
	LAST_CLIENT_REMINDER_DATE,
	LAST_EMAIL_SEND_DATE,
	LAST_REMINDER_DATE,
	LAST_STATUS_UPDATE_TIME,
	LAST_UPDATED,
	LOCATION_ID,
	OPEN_YEARMONTH,
	PRIORITY_TYPE_ID,
	PRIORITY_TYPE_NAME,
	PROBLEM_TYPE_ID,
	PROBLEM_TYPE_LONGNAME,
	PROBLEM_TYPE_NAME,
	QUESTION_TEXT,
	REPORT_DATE,
	Floor(REPORT_DATE) as Date,
	STATUS_TYPE_ID,
	STATUS_TYPE_NAME,
	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,
	SUBJECT,
	TICKET_TECH,
	TICKET_TIME_AS_OF_LAST_UPDATE,
	UPDATED_BY_TECH_FLAG,
	UPDATED_FLAG,
	WORK_END_DATE,
	WORK_START_DATE

Ticket_dates:

	JOB_TICKET_ID,
	Date,
	(TimeStamp(REPORT_DATE, 'YYYY-MM-DD hh:mm')) as Ticket_Report_Date,
	Ticket_Close_Date,
	(Date(Date, 'YYYY-MM')) as Ticket_Open_YearMonth,
	(Date(CLOSE_DATE, 'YYYY-MM')) as Ticket_Close_YearMonth,
	LAST_STATUS_UPDATE_TIME,
	if(CLOSE_DATE <> LAST_STATUS_UPDATE_TIME, LAST_STATUS_UPDATE_TIME, CLOSE_DATE) as Ticket_Last_Updated,
	TICKET_TIME_AS_OF_LAST_UPDATE,
	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

MasterCalender:

	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

Re: For each loop - creating a summary of tickets pr month

Wed, 30 Oct 2013 12:07:03 GMT

Jeg vil lave C# script som laver en array-liste hver måned(eller dag om du vil), hvor den så kigger på om der kommer nogle nye tickets ind. Er der kommet en ny ind, så må være der en ny opened, er der 1 mindre,, så er der en lukket.

Du kan sammenligne client_id vil jeg tro.

Her er et eksempel på en kode som næsten gør det samme:

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Collections.Generic;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

List<int> baselineCustomerList = new List<int>();

List<int> currentCustomerList = new List<int>();

string optionFirstRun = "yes";

string lastDate = "2013-03-22";

string lastOptionID = "Ukendt";

bool monkey = false;

int RowCount = 0;

public override void Input0_ProcessInputRow(Input0Buffer Row)

{

RowCount++;

if (!lastDate.Equals(Row.DateClean))

{

optionFirstRun = "no";

if (currentCustomerList.Count > 0)

{

OutputBuffer.AddRow();

OutputBuffer.Status = Row.Status;

OutputBuffer.AfgangHovedfase = removeAdditions(baselineCustomerList, currentCustomerList);

OutputBuffer.TilgangHovedfase = findAdditions(baselineCustomerList, currentCustomerList);

OutputBuffer.DateClean = lastDate;

OutputBuffer.Milestone = lastOptionID;

currentCustomerList.Clear();

lastDate = Row.DateClean;

lastOptionID = Row.Milestone;

}

if (optionFirstRun.Equals("yes"))

{

baselineCustomerList.Add(Row.ItemID);

lastDate = Row.DateClean;

lastOptionID = Row.Milestone;

}

else

{

// er option skiftet? Hvis ja, s�t firstRun = yes og start forfra p� opfyldning af baseline kundeliste

if (lastOptionID != Row.Milestone)

{

optionFirstRun = "yes";

baselineCustomerList.Clear();

baselineCustomerList.Add(Row.ItemID);

lastOptionID = Row.Milestone;

lastDate = Row.DateClean;

currentCustomerList.Clear();

}

else

{

currentCustomerList.Add(Row.ItemID);

// ComponentMetaData.FireInformation(0, null, "currentCustomre customer add ", null, 0, ref monkey);

lastDate = Row.DateClean;

lastOptionID = Row.Milestone;

}

if (RowCount==Variables.RowcountFactCRMFlowMain)

{

OutputBuffer.AddRow();

OutputBuffer.Status = Row.Status;

OutputBuffer.AfgangHovedfase = removeAdditions(baselineCustomerList, currentCustomerList);

OutputBuffer.TilgangHovedfase = findAdditions(baselineCustomerList, currentCustomerList);

OutputBuffer.DateClean = lastDate;

OutputBuffer.Milestone = lastOptionID;

}

/// <summary>

/// Finds a count of elemtents in newLineListe that is not present in the baseLineList

/// </summary>

/// <param name="baseLineList"> the baseline list</param>

/// <param name="newLineList">the list to check if there is items not in the baselinelist </param>

/// <returns></returns>

private int findAdditions(List<int> baseLineList, List<int> newLineList)

{

int additionCount = 0, baseLineCount = 0;

foreach (int item in newLineList)

{

if (!baseLineList.Contains(item))

{

additionCount++;

baseLineList.Add(item);

}

baseLineCount++;

}

return additionCount;

}

/// <summary>

/// Counts items which is in baselist and not in new list

/// </summary>

/// <param name="baseLineList"></param>

/// <param name="newLineList"></param>

/// <returns></returns>

private int removeAdditions(List<int> baseLineList, List<int> newLineList)

{

int removeCount = 0;

for (int i = baseLineList.Count - 1; i >= 0; i--)

{

if (!newLineList.Contains(baseLineList))

{

baseLineList.Remove(baseLineList);

removeCount++;

}

return removeCount;

}

Re: For each loop - creating a summary of tickets pr month

Wed, 30 Oct 2013 12:21:04 GMT

How is Tickets_Carried_Over defined?