Need to create a ticket card which shows all raised, closed and backlog tickets
Inflow : Total tickets raised in the week
Outflow: Total tickets closed in the week
Backlog: Incremental open ticket count required as per week
Beast mode created :
inflow: count(CASE
WHEN `status` <> 'Cancelled'
THEN `incidentid`
END)
Outflow: count(CASE
WHEN `status` = 'Closed'
THEN `incidentid`
END)
Backlog: COUNT(CASE
WHEN `status` <> 'Closed'
THEN `incidentid`
END)
Best Answers
-
You'll want to use the Line + Grouped Bar chart type and then go to chart properties > General > Number of Running Total Lines. See below:
Configure the chart so that Backlog is a running total
If I solved your problem, please select "yes" above
1 -
Just keep in mind that this will show you the running total on the time window you're displaying, not the all time running total, it'll start with the delta between the values of the first week displayed and add or subtract based on the weekly delta from then on. Just making this clear as I'm not sure what from the beginning might mean to you.
0 -
If I were needing to do this, I would use a SQL dataflow. Something like this:
SELECT `date` , `incident id`, `date` as `date 2`, COUNT(DISTINCT CASE WHEN `date` < = `date2` THEN `incident id` END) as 'Running Total' FROM 'Dataset' WHERE `status` <> 'Closed' GROUP BY `date 2`
If I solved your problem, please select "yes" above
0
Answers
-
Hi Dheeraj, it seems like you have built the card. What are you having trouble with?
If I solved your problem, please select "yes" above
0 -
The issue is that, the count of backlog should be incremental as per week.
Example: The backlog should be the total open tickets present in the week from the beginning. in the card it shows only for that week.
0 -
You'll want to use the Line + Grouped Bar chart type and then go to chart properties > General > Number of Running Total Lines. See below:
Configure the chart so that Backlog is a running total
If I solved your problem, please select "yes" above
1 -
Just keep in mind that this will show you the running total on the time window you're displaying, not the all time running total, it'll start with the delta between the values of the first week displayed and add or subtract based on the weekly delta from then on. Just making this clear as I'm not sure what from the beginning might mean to you.
0 -
I meant it should show all the pending items from the time data set was developed.
0 -
If I were needing to do this, I would use a SQL dataflow. Something like this:
SELECT `date` , `incident id`, `date` as `date 2`, COUNT(DISTINCT CASE WHEN `date` < = `date2` THEN `incident id` END) as 'Running Total' FROM 'Dataset' WHERE `status` <> 'Closed' GROUP BY `date 2`
If I solved your problem, please select "yes" above
0 -
I had a similar request, where the Running total needed to show the all time history and took me a while to find a way that could more or less work for what I wanted, sadly it kind of renders the default date filters useless. Here is how I achieved this.
1) Create a beast mode that will transform all dates prior to where you want the graph to start to a week prior to that date (let's call it
GraphDate
, this can be controlled via a variable of just knowing it'll be X weeks from now, something like:CASE WHEN CURDATE() - INTERVAL 10 WEEK >
date
THEN CURDATE() - INTERVAL 10 WEEK ELSEdate
ENDThis will make the all entries older than 10 Weeks ago have the date for 10 weeks ago, which will be the first entry to show, so you know how you started prior to the period of changes you care about.
2) Your graph will be based on this new calculated field, All Time Graph by Week.3) You use the same configuration mentioned to have a running total line.
The outcome would be a graph that looks similar to (mine is monthly):
Hope this helps, not perfect but at least will get you closer to where you want to be.
0 -
Hi Marcel,
Thanks for your response, in our case when we apply the above condition the entire data in dataset gets affected.
The inflow and the outflow column are getting affected. We only want the running total for the Backlog count.
Can you please suggest?
0 -
This is expected, since to determine the first value you'll need how many Inflows and Outflows had happened until that point in time, if you see my card has two big chunks on the first displayed value while the rest of the entries do show what you would expect. Let me know if this is what you were talking about, if not if you can share a screenshot on what you see vs what you'd expect to see that might provide us more insights.
0 -
Agreed with your points, our visualization should not show the two big chunks of inflows and outflows. we want a visualization which will give the total backlogs even if we change the date of the graph.. the backlog tickets should be incremental in nature.
eg: if I have 1 tickets in 1st week, 2 tickets in 2nd week.. the Graph should show 3 tickets in the second week.0 -
The backlog should be incremental if you selected the option as @colemenwilson described. However I was unable to get this to work without showing the big chunks at the start (Rolling Totals need to have a start value and add the delta on each period). For this to work you shouldn't change the Date Range of the chart itself, but control that via variables instead.
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 708 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive