Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Counting number of tickets opened in a month

Hi!

I have been trying to find an answer without a success from the community. My problem is that I have a list of tickets that have been opened during the past six months. I need a table that shows how many tickets were opened last month per category. My fields are:

  • Resolution Category Tier 1
  • Submit Date
  • Incident ID

Background information: There are plenty of other date based columns in the data as well.

1) With the following expression I managed to get all the tickets per Resolution Category Tier 1.

count({<[Resolution Category Tier 1]={Työasemainfra}>}[Incident ID])

2) I tried the following expression to filter only tickets opened in February, but I the answer is 0.

count({<[Resolution Category Tier 1]={Työasemainfra},[Submit Date]={">=1.2.2017<=28.2.2017"}>}[Incident ID])

3) I tried the following expression to filter only tickets opened in February, but I the answer is same as in case 1).

count({<[Resolution Category Tier 1]={Työasemainfra},date={">=1.2.2017<=28.2.2017"}>}[Incident ID])

What should I do?

Bonus question: Is there a way to automatize this so that I don't need to change the dates every month for a new report?

BR Tiia

1 Solution

Accepted Solutions
Not applicable

Re: Counting number of tickets opened in a month

Here's how I solved this:

Measure:

count({<[Resolution Category Tier 1]={NameOfTheCategory}

, [IncidentReportedMonth] = {"$(=$(vPreviousMonth))"}

}>}[Incident ID])

Variable:

vPreviousMonth=MonthStart((AddMonths(Today(),-1)))

In Data Load Editor:

monthstart(date([Submit Date])) as [IncidentReportedMonth]

     where [Submit Date] is column name where ticket's opening date is.

11 Replies
serj_shu
Valued Contributor

Re: Counting number of tickets opened in a month

Hello, Tiia!

Ok, let's do this without any example of data. Make it hard!

Please, find the picture below where I tried to make it step by step with a phony generated data:

tickets.png

This will help you to pick every previous month without any hard coding.

Not applicable

Re: Counting number of tickets opened in a month

Hi!

Thanks for the advice. Unfortunately I get just rows of – instead of ticket quantities. This was the expression I used:

count(DISTINCT(<[Submit Date]={">=${=monthstart(addmonths(max(),-1)))<$(=monthstart(max()))"}>}[Incident ID])

My Incident_ID=ticket_ID has both numbers and letters (e.g. INC1000101). Could this affect this calculation?

Plus I need to have tickets from only one of the Resolution categories (e.g. from category A in your example). How do I add that?

Below is example of the fields I use and how I would do this in Excel, and how I would like the table to look like (Tickets column)

BR Tiia

serj_shu
Valued Contributor

Re: Counting number of tickets opened in a month

Hello, Tiia!

If I understood you correct you can solve your task with a straight table:

tickets2.png

Not applicable

Re: Counting number of tickets opened in a month

Hi!

I'm not quite certain what you mean by cyclic dimension. It seems that you have Tier1 as rows which isn't quite what I need. In pivot I would have Resolution Category Tier 1 as Filter to show only one category (Työasemainfra), and some specific month (from Submit date). Rows would be Resolution Category Tier2 categories, and values Count of Incident ID.

serj_shu
Valued Contributor

Re: Counting number of tickets opened in a month

My opologies, Tiio!

Please, find below a new solution!

tickets3.png

I used T in the first tier istead of Työasemainfra, hope you don't mind!

Not applicable

Re: Counting number of tickets opened in a month

Hi!

For some reason I don’t get any values just –. Please see the picture.

Is the expression written right?

count(distinct{<[Submit Date]=(">=$(=MONTHSTART(max())<$(=monthend(max())"},[Resolution Category Tier 1]={'Työasemainfra'}>}[Incident ID])

BR Tiia

Highlighted
bvk
Contributor II

Re: Counting number of tickets opened in a month

Hi Tiia,

This kind of calculations are typically done through the use of a Master Calendar. This is a auto generated table starting from the beginning of your data set to the end. (see for more infomation here:Understanding the Master Calendar (video)

You then set for each date, the month, day, week, year etc. but you also create flags (0 or 1) for things like current month, previous month, current year, previous year etc.

These flags can the easily be used in your set analysis expressions and will always update on each reload.

Regards,

Bas.

serj_shu
Valued Contributor

Re: Counting number of tickets opened in a month

Hello, Tiia!

In your expression: count(distinct{<[Submit Date]=(">=$(=MONTHSTART(max())<$(=monthend(max())"}

max() of what?

There should be a date field.

Not applicable

Re: Counting number of tickets opened in a month

I think I originally had Submit date, but any ways I tried it now and it just gives me -. I took another approach:


count({<[Resolution Category Tier 1]={CategoryA},[Submit Date.autoCalendar.Month]={Feb}>}[Incident ID])


Do you have any suggestions how to change Feb to previous month? Would it be possible to use MonthAgo and MonthsName functions?


BR Tiia

Community Browser