Want to add minutes of a particular day from different rows

Dheeraj_1996

I want the sum of downtime in minutes for a particular day. Eg: 14th May had a total downtime of 33 minutes. I want to have a single row for 14th May with downtime 33 minutes.

How can this be done?

ColemenWilson

In a table card:

1. Add the date field
2. Add the downtime in minutes field
3. Change the aggregation for the downtime in minutes field to SUM as shown below:

marcel_luthi

It all depends on what want to achieve. In general if you just want to display the Date with the Total minutes you can achieve this the way @colemenwilson suggested, selecting only these 2 fields and setting your card to display by Day. This will give you a single entry per day with the Total Downtime for and this will even allow you to switch to see total downtime on different periods.

If you add other fields to the card, you'll get the total downtime at that granularity, like if instead you have Date, Ticket and SUM (Downtime) then what you'll be showing is the total daily downtime by ticket.

On the other hand if what you need is to show all entries but want the total Daily Downtime as another column, you can do this via BeastMode with the FIXED function (so that it'll be an additional column in the table you shared):

SUM(SUM(`Downtime in Minutes`) FIXED (BY DATE(`Start Time`)))

Keep in mind that regardless of the approach you'll need to define how you want to address if there is downtime entries that span across multiple days, like if it stars 2022-08-15 23:20:00 and ends 2022-08-06 01:17:02.

Hope this helps!

ColemenWilson

Make sure you have Aggregation set to Sum for the minutes_new field:

Your screenshot cuts off but if any unique values show in the table other than date it won't aggregate by date. Also check the sorting section in analyzer. If there is a sort enabled there with a field that is a timestamp for example then it won't aggregate properly.

ColemenWilson

You can either use subtotals and totals on your table or remove the unique fields from the table and aggregate by Date by summing Downtime in Minutes. If you add unique fields the table expands (unaggregates) to show the unique values.

So for the 14th of May, you have the start and end time fields, the Ticket ID, etc.. to show these the table has to expand and cant have a single row for the 14th because there are many different values for those columns: 02:19:00, 02:01:00, CRQ000003578158, CRQ000003571961, etc…

Dheeraj_1996

any other way to add up the minutes for particular day?

ColemenWilson

In a table card:

1. Add the date field
2. Add the downtime in minutes field
3. Change the aggregation for the downtime in minutes field to SUM as shown below:

marcel_luthi

It all depends on what want to achieve. In general if you just want to display the Date with the Total minutes you can achieve this the way @colemenwilson suggested, selecting only these 2 fields and setting your card to display by Day. This will give you a single entry per day with the Total Downtime for and this will even allow you to switch to see total downtime on different periods.

If you add other fields to the card, you'll get the total downtime at that granularity, like if instead you have Date, Ticket and SUM (Downtime) then what you'll be showing is the total daily downtime by ticket.

On the other hand if what you need is to show all entries but want the total Daily Downtime as another column, you can do this via BeastMode with the FIXED function (so that it'll be an additional column in the table you shared):

SUM(SUM(`Downtime in Minutes`) FIXED (BY DATE(`Start Time`)))

Keep in mind that regardless of the approach you'll need to define how you want to address if there is downtime entries that span across multiple days, like if it stars 2022-08-15 23:20:00 and ends 2022-08-06 01:17:02.

Hope this helps!

Dheeraj_1996

thanks a lot.. this helped but this still shows duplicate entries. How do I get single entry for that day?

ColemenWilson

Make sure you have Aggregation set to Sum for the minutes_new field:

Your screenshot cuts off but if any unique values show in the table other than date it won't aggregate by date. Also check the sorting section in analyzer. If there is a sort enabled there with a field that is a timestamp for example then it won't aggregate properly.