Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

11 Replies
Highlighted
Specialist
Specialist

Hello, Tiia!

Can you provide some data sample (scrambled or manually generated), which will fit your original data. Beacause there are multiple solutions depend on which data you have.

Highlighted
Not applicable

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.

View solution in original post