Need to create a ticket card which shows all raised, closed and backlog tickets

Dheeraj_1996

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)

ColemenWilson

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

marcel_luthi

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.

ColemenWilson

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`

ColemenWilson

Hi Dheeraj, it seems like you have built the card. What are you having trouble with?

Dheeraj_1996

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.

ColemenWilson

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

marcel_luthi

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.

Dheeraj_1996

I meant it should show all the pending items from the time data set was developed.

ColemenWilson

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`

marcel_luthi

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 ELSE date END 

This 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.

Dheeraj_1996

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?

marcel_luthi

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.

Dheeraj_1996

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.

marcel_luthi

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.