Want to add minutes of a particular day from different rows

Dheeraj_1996
Dheeraj_1996 Member
in Beast Mode

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.

image.png

How can this be done?

Tagged:

Best Answers

  • ColemenWilson
    ColemenWilson Coach
    edited August 2023 Answer ✓

    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:
    Screen Shot 2023-08-16 at 10.01.24 AM.png

    If I solved your problem, please select "yes" above

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    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.

    image.png

    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
    ColemenWilson Coach
    edited August 2023 Answer ✓

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

    Screen Shot 2023-08-17 at 7.28.34 AM.png

    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.

    If I solved your problem, please select "yes" above

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited August 2023

    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…

    If I solved your problem, please select "yes" above

  • Dheeraj_1996
    Dheeraj_1996 Member

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

  • ColemenWilson
    ColemenWilson Coach
    edited August 2023 Answer ✓

    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:
    Screen Shot 2023-08-16 at 10.01.24 AM.png

    If I solved your problem, please select "yes" above

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    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.

    image.png

    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
    Dheeraj_1996 Member

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

    image.png
    image.png

  • ColemenWilson
    ColemenWilson Coach
    edited August 2023 Answer ✓

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

    Screen Shot 2023-08-17 at 7.28.34 AM.png

    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.

    If I solved your problem, please select "yes" above

Categories