Hi All,
I am trying to generate some insights related to a dataset containing ticket information. The source data arrives in form of daily .csv file exports from our ticket management system. Through some processing in Alteryx, I convert this into something similar to the 'MasterSource' sheet in the attached Excel file.
Ultimately, I am using Alteryx to produce data for trending in an Excel dashboard. The dashboard has a set of dropdown filters that will only display data relating to the selected filter. I have logic in my Excel dashboard that will select the required column(s) from the Alteryx output (which sits in a separate sheet) to plot data relevant to the filter. To accomplish this, each ticket has a ConcatFilter field which concatenates the filterable properties together.
I have accomplished this successfully for the number of tickets created and completed over time by the following tools:
- Summarize Tool
Group By: Completed Date
Group By: ConcatFilter
Count Distinct: Ticket Number
- Cross Tab
Group By: Completed Date
Change Column Headers: ConcatFilter
Values For New Columns: Completed Count
Method For Aggregating Values: Sum
This produces individual columns for the Created and Completed counts for each ConcatFilter on each day.
Another metric I want to trend is the number of overdue tickets over time. I have a method of doing this in Excel, however I want this to be done in Alteryx to reduce the workload on individual users machines when they change filters in the dashboard. A column with the number of overdue tickets is to be produced for each individual ConcatFilter.
Description of 'MasterSource' data (see attached Excel sheet):
- Ticket Number: unique identifier.
- Created On: when it was created.
- Overdue Date: the date after which the ticket is considered overdue.
- Completed Date: when the ticket was completed, is null if the ticket has not yet been completed as of the latest export.
- Concat Filter: various properties of the ticket concatenated together to create a single filter.
The criteria for the number of overdue tickets on any particular [Date] is:
- Created On before [Date]
- Completed Date after [Date]
- Overdue Date before [Date]
- ConcatFilter equals that selected by the user.
I have successfully implemented this logic in Excel but unsure how to go about it in Alteryx. See the attached file for how it works in Excel.
Thanks In Advance!
