Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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
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.
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:
This will help you to pick every previous month without any hard coding.
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
Hello, Tiia!
If I understood you correct you can solve your task with a straight table:
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.
My opologies, Tiio!
Please, find below a new solution!
I used T in the first tier istead of Työasemainfra, hope you don't mind!
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
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.
Hello, Tiia!
In your expression: count(distinct{<[Submit Date]=(">=$(=MONTHSTART(max())<$(=monthend(max())"}
max() of what?
There should be a date field.
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