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(B724),“:”,(B724-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 …
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 , 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?
[Add Support for Temporal Columns in the Group Aggregate UX Options](https://community.appsheet.com/t/add-support-for-temporal-columns-in-the-group-aggregate-ux-options/34302) Feature Requests
UX>Views>Group Aggregate This feature is very useful for summarizing data because the SUM, TOTAL, AVG, etc- all update as you click into different groups. It’s not tied to totalling any one grouping, but rather, dynamically totalling different records on the fly-- as the user clicks into each group. [Screen Shot 2020-11-13 at 6.34.25 AM] However, it does not work with temporal column types (Date, Time, DateTime, Duration). A virtual column could be used to calculate the SUM, TOTAL, AVG, etc,…
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
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.
[Is it possible to create a Stack chart using aggregate (group / count) of columns?](https://community.appsheet.com/t/is-it-possible-to-create-a-stack-chart-using-aggregate-group-count-of-columns/12910) Questions
Hi, is it possible to create a Stack chart using aggregate (group / count) of columns? Selecting Horizontal Histogram I have then the option to group aggregate via Count: [image] However, when selecting Row Series (Stack) chart type, there is no option to Group Aggregate. Furthermore, I can not see any of my columns in the drop-down… [image] I want to create this horizontal bars chart of Abandonments by month, but Stacked: i.e. composed of Justified (green) and Not Justified (red) abandonme…
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?
@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?
[Sum of all monthly profits](https://community.appsheet.com/t/sum-of-all-monthly-profits/10210) Questions
My app is used for creating and managing support tickets for computer repair. Got everything working including contacts; a form for adding new contacts; a dashboard to see all tickets in progress and filtering by state (in progress, pending, ongoing); a form for creating new tickets and automatically doing all the math for transportation, labor, etc and creating a total; a menu view for all completed tickets; everything is all working nicely together and I’ve been using the app for about 6 month…
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.
