Solved: Aggregating hh:mm data in Google Sheet for Chart.....

Stuart_Uren · 02-05-2021 02:09 AM

I have a Property Maintenance app I created which has a table containing hours worked in Duration (hh:mm) format, e.g. 02:30.

I am now wanting to create a chart off this table, grouping the job and summing hours per job. After some research, I understand I need to do this in Google Sheet via the query() function, then present the data to Appsheet as read only data.

However, the sum() function in Google Sheet gives an error as the field format is not a number, so I’ve figured out the query after some time which looks like this:

=ArrayFormula(query({Hours!B:B, TO_PURE_NUMBER(Hours!D:D)},
“select Col1, sum(Col2) group by Col1”
,1))

Just taking one line of output I see my 10:45 (it’s a large garden!) is converted to a numeric as a fraction of a day.
Dig garden 0.4479166667

I’m now wanting to convert this back to a hh:mm format, and so far I have this formula where column B in the above spreadsheet output is the 0.4479166667 value.
=concatenate(floor(B7*24),":",(B7*24-floor(B7*24))*60)
which now gives me my 10:45 answer.

I’m wondering how I put this concatenate in the above ArrayFormula(query(…) in Google Sheet.
Nested query?

Just wondering if anyone else has done this before, and if there is an easier way?!

I’m also presuming the AppSheet Chart will also like the hh:mi data …

Thanks in advance.

Stuart_Uren

Further to the above, I revisited that Google Sheet ArrayFormula(query() function and got it to work. Just two minor tweaks from what was in my first post:

multiplied the sum of Col2 (total duration for job) by 24, as calculation was based on a day. This set the example above answer to 10.75. Figured AppSheet would like a numeric better than a duration/time, so left it as this.
added: where Col1 is not null
this removed a blank line between the header and data.

=ArrayFormula(query({Hours!B:B, TO_PURE_NUMBER(Hours!D:D)}, "select Col1, sum(Col2)*24 where Col1 is not null group by Col1 label sum(Col2)*24 'SumHours'",1))

After getting the G sheet to desired result, I’ve created the chart off it, and it looks good.
The minor caveat is that each time I want to see refreshed data for the chart I’d have to resync.

This may be an unorthodox means to get a result, but in this case, it did work.

View solution in original post

Steve

This surprises me. Can you elaborate?

Stuart_Uren

@Steve - I came to the conclusion I had to go down the Google Sheet query() function path after looking at this post. Maybe not the deep dark rabbit hole after all?

Stuart_Uren

Further to the above, I revisited that Google Sheet ArrayFormula(query() function and got it to work. Just two minor tweaks from what was in my first post:

multiplied the sum of Col2 (total duration for job) by 24, as calculation was based on a day. This set the example above answer to 10.75. Figured AppSheet would like a numeric better than a duration/time, so left it as this.
added: where Col1 is not null
this removed a blank line between the header and data.

=ArrayFormula(query({Hours!B:B, TO_PURE_NUMBER(Hours!D:D)}, "select Col1, sum(Col2)*24 where Col1 is not null group by Col1 label sum(Col2)*24 'SumHours'",1))

After getting the G sheet to desired result, I’ve created the chart off it, and it looks good.
The minor caveat is that each time I want to see refreshed data for the chart I’d have to resync.

This may be an unorthodox means to get a result, but in this case, it did work.

Koichi_Tsuji

I would suggest that we explore the solutions within Appheet and with Appsheet expression rather than do bits and pieces with spreadsheet formula.
This is best practice.
Appsheet expression can do what you want. Most importantly, more effective and powerful.

Question here is if you are ready to get your hands dirty to deal with (sometimes to struggle with) Appsheet.

Based on my past experiences with Appsheet for years, they never turn donw my expectation.
Almost anything is possible with Appsheet. just we need to be bit creative, thats it.

Stuart_Uren

Appsheet is a great product. From what I see, it’s worth the effort pushing through challenges like this.

GreenFlux

@Stuart_Uren , I agree with @tsuji_koichi - it’s best to exhaust all options in AppSheet before resorting to sheet formulas.

There are some limitations with summing times/durations, though. Is this what you’re talking about?

Koichi_Tsuji

Additions to my previous comments.

Not exactly sure what your final goal to achieve.

If your question is about math for duratio n type column it should work as normal plus minus expression. [Duration A]-[Duration B] or [Duration A]+[Duration B]. Both should work.

The result should stay as Duration type.

If you want to conver to duration onto numeric values, I would suggest that you test to conver your duration value to decimal values and do the caluculatoin what you want based on the converted value then consume within Chart as Chart if you want to this result as measure rather than dimension.

To conver the duration type fields and values to decimal in Appsheet, the basic syntax should be

HOUR([Duration])+MINUTE([Duration])/60.00+SECOND([Duration])/60.00/60.00

Again, im not sure if this help you to solve your issue.

Steve

Koichi_Tsuji

Thanks @steve
Yes, simple enough.

Stuart_Uren

Thanks Guys.
The problem with being a novice is that sometimes you go down dark rabbit holes…

The end result I am looking to achieve is show: Job, Sum(hours), but show this in a Chart.

I found this post which is kind of similar to what I’m wanting to do.

When I look at tutorials (this one from Deep Dive: Expressions 2), I see totals in single VC’s but never in a detail format.

I did create a sheet with single column, then added 2 VC’s, one getting a list of Jobs, the other getting the sum of hours for that Job. However, it seems I’d have to create a VC for every individual job + another for the aggregate, thus ending up with a large number of columns that I’d have to keep adding to?

Or do I look to create a new table which is built from aggregate data off my Hours data?

Just looking for direction.

Aggregating hh:mm data in Google Sheet for Chart... is there an easy(er) way?