I want to display blood pressure averages for different hours in day. Glide Chart displays the count (of measures). How is it possible to have averages instead of count? Be aware that it is needed for FILTERED list of measurements (e.g. hours 8-12)!
Not exactly sure what you mean by that?
“hours 8-12” - does that mean you only want to display data for 8am, 9am, 10am, 11am and Noon?
Anyway, what I would do is create a helper table with one row for each hour of the day that I want to chart data for. Then relate that to the source data, create whatever rollups are necessary, and use that table as the source of the chart.
Sorry for my poor explanation!
In this filtering case I want to have 5 bars with AVERAGES instead of COUNT, as it is now, see pisture:
Additional tables I don’t like, because I want to have multiple filtering, e.g. on dates for differentiating summer and winter etc. Glide chart counts correctly the instances from data filter, where is the problem with sum, average etc? Somenone wants to have income for weekdays, this is the sum of all transations, where is the problem?
Okay, so you’d need to create a multiple relation column that matches the data to itself based on the hour of the day, then create a rollup->average via that relation. You should then be able to use that rollup in your chart.
But as I said, for greater flexibility, a helper table is the way to go. That’s my advice, take it or leave it.
I am always striving for simple and effective solutions. Before your suggestion to use relation+rollup I came also to that as a kind of workaround in the situation where in Glide Charts doesn’t support simple AVERAGE. I got interesting result yielding SUM of my numbers in the chart instead of AVERAGE. This is of course wrong, but I used math column to divide every number with count from rollup. This was long workaround to get simple average in the chart:
This is very nice how it updates the graph when changing the filter values!
My hard felings against the helper table are caused by such violating database normal form!
Anyhow one additional question: how do I get weekday from the date? Somewhere in the commmunity I have seen "today(weekday), but this funkcion I can’t find in Glide editing.
The method I use to get the weekday from a date is as follows:
- Firstly, I create a column that contains the names of the days of the week (Sun, Mon, Tue, etc). This column can be in any table (I usually have it in a Lookup/Helper table)
- Then I use the following Math formula on my Date column:
– This returns an integer between 0 and 6, representing the day number of the week, beginning with Sunday == 0
- I then use a Single Value column to fetch the Day name from my list of days, using
Nfrom start, where
Nis the result of the math column.
A simpler option is to use the Format Date plugin, however that plugin is known to give inconsistent results with Safari on IOS, so I don’t use it for that reason.
Tnx a lot, I wqasn’t aware of Weekday(Date) function!? Shame on me, where is the list of all functions available in math?
Btw, converting 1 to MON etc I am doing with ifs instead of lookup!
Here’s a list of supported math functions. I think this is most of them. There might be a couple that are missing from this list, but usually you can infer what may be missing, or just ask. Maybe we need to make sure that glide has a complete documented list.
Yes, some people use that method.
I don’t normally recommend it though, because it’s quite a bit of extra work (especially if you have to do it more than once) and much easier to make a mistake.
Tnx, this is helpful! nevertheless one simple question: when editing Math Column, why there is no drop down for selecting these functions? I don’t imagine that Glide will emulate ALL Excel functions, but the list of functions with erguments specification would be very helpful!
Btw, why there are no text functions (FIND, PARSE etc?).
That would be useful, but I’ve been familiar with these functions for a long time, so it’s natural for me to use them without thinking about it. It’s not much different than using these same functions in other programming languages. It would probably be a little more complicated for glide to build a user friendly interface that also works with some of the complex math formulas we have come up with.
My idea here was to have Text column with some elementary text manipulation functions. This would be fine exatly for 1,2,3,… conversion into MON, TUE, WED, … instead of your lookups and my ifs!
For ideas to simplify things, you could always create a feature request that maybe Glide would consider.
I am using Excel formulas already, but the problem is adding new rows. How do I copy the formula in the new row?
I’m only talking about Glide functionality. I very rarely use formulas in the sheet itself. It’s better to do them within glide, because it will be easier and provide a much better end user experience, due to zero delay in waiting data to sync back from the sheet.
But to answer your question, if you ever had no choice but to use formulas in the sheet itself, Google Sheets has ArrayFormulas to populate a formula across all rows. I don’t use Excel, so I don’t know if they have a similar functionality or how it would be formulated. Either way, if you can do it in Glide, I would recommend doing that.
(Unrelated to the topic but related to the screenshot above: I never knew one could do a search for the column type. It’s probably been there forever but I never noticed it. Very nice.)
hehe, I’ve been using that ever since they rearranged the column menus and hid joined list and single values.
It only took me about 6 months to notice then
Array formulas un Googlesheets are ok for static tables. I naven’t found the mean to add a row when using array formulas: if I have 10 rows of data and define arrayformula of 1000 rows, glide adds the new one as 1001st row!
Now I solved everything with filter values in Users’ table (userspecific) plus several filter columns in working table. This is very good, but there is stil one problem: for that I have to have registered and signd-in users. This is not applicable for the users working without sign-in! Is there a solution for that?