Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

For each loop - creating a summary of tickets pr month

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
2 Replies
Not applicable
Author

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;

    }

Not applicable
Author

How is Tickets_Carried_Over defined?