I need to calculate cumulative backlog in card for the data filtered by various categories and date

mahee84 · September 2021

This is the data for my chart. I have to show ticket created count ,ticket closed count and cumulative backlog count datewise . The dynamic filters are severity, domain, sub domain and date. The chart has to be look like below. The below chart is taken from excel report. I have to achieve this in domo. please provide ideas .

amehdad · October 2021

Hi @mahee84, in order to get that graph view, follow these steps:

Select 'Line + grouped bar' in Chart Types
Drag in your 'Cumulative Backlog' field in the X axis and then either Daily date fields in 1st Series spot and then the other Daily date field in 2nd Series column
At the moment, it'll show as one bar and one graph so then you'll go into Chart Properties -> General -> Series on Left Scale and type in '2'
Once applied, it'll show as 2 lines which then means you should add in your 'Ticket No' field to the 3rd Series column, resulting in a bar with two lines
Afterwards, it's tidying up the y-axes in Chart Properties -> Value Scale, and other elements to match your Excel report.

jaeW_at_Onyx · October 2021

cumulative backlog is just a window function

sum(sum(1)) over (order by date)

the trick is to know if on a date the ticket is still open

case when date<= closeDate then 1 else 0 end.

course this only works assuming you have a ticket on every single day or you might end up with gaps in your data.

https://www.youtube.com/watch?v=ZPf41Fjn1H8&t=592s

good luck!

I need to calculate cumulative backlog in card for the data filtered by various categories and date

Answers

Categories