Hi, I have an app the collects multiple daily...

(D. Robert Charlon) #1

Hi, I have an app the collects multiple daily entries in a table, and I’m trying to find a way to aggregate and sum those entries by day so that I can create a chart view that displays daily totals. I’m not sure how to go about doing that. Do a need a separate worksheet? I’ve also seen some examples that use a virtual column with a sum select expression, but they’re not quite the same thing. Any ideas?

(Aleksi Alkio) #2

Another solution if you can’t aggregate your data for some reason… you can create a separate summary table in your spreadsheet. With the gSheet you can have that with the QUERY expression and then use that table as a source for your chart view.

(D. Robert Charlon) #3

Hi, thank you for the responses. To update, I have managed to create a separate summary table with a virtual column that aggregates the dates, one that lists the percentage for each entry and one that averages percentage values for all date entries, but I am having trouble figuring out an app formula that brings all those together so that I get an average percentage per day. Wouldn’t an AVERAGE(SELECT) expression do the trick? Would it be built from conditions of the virtual columns in the summary table? The virtual columns are labeled [Dates], [Percentages], and [Average]. So I was thinking that a virtual column with the app formula: AVERAGE(SELECT([Percentages], [Dates] = ?)) I was hoping to make this work on the app side to decrease latency and sync time.

(Suvrutt Gurjar) #4

Hi +David Charlon,

As per my understanding , a simple averaging of all individual averages may lead to error in results, if the goal or denominator for each individual average

is not same.

As per my understanding if you are computing average for each row entry

by say [Column A] / [Column B], then for proper average value across the particular date will be given by expression

(DECIMAL(SUM(SELECT(Table Name[Column A],[Dates]=[_THISROW].[Dates]))))/(DECIMAL(SUM(SELECT(Table Name[Column B],[Dates]=[_THISROW].[Dates]))))

(D. Robert Charlon) #5

Hi, @Suvrutt_Gurjar, I didn’t do a very good job of explaining the calculations I’m using. The multiple daily entries are records of responses in clinical trials where the response is either “correct” (1), or incorrect/prompted, which calculates to (0). So my percentages are really just a list of 1s and 0s derived from a correct over total calculation. There might be anywhere from 5-20 rows of these trials per day in a “Trails” table. I am wanting to generate, from these, a daily average. I realize it was inaccurate to describe them as Percentages, they are really just values that get converted to percentage in my google sheet. My apologies for the confusion. I am new to appsheet and not really that experienced in working with spreadsheets in general.

Can I take the expression you’ve written above and use that as the app formula in a virtual column based on my summary table? Would this be easier if I linked you the app?

Thank you for your help.

(D. Robert Charlon) #6

In other words, all I want to do is to create a line chart view that does this:

(D. Robert Charlon) #7

Somehow the system is able to generate an average of a percentage column, sorted by date. How do I recreate this manually? On my sheet, there is a column that calculates “[+]” to 1, and a column that calculates “Voc” to 0, and a final column that resolves those values to either 100% or 0%.

(Suvrutt Gurjar) #8

Hi +David Charlon, Thank you for your detailed input. Also looks like you have already managed to get averages. Great!

In either case( AppSheet formula or spreadsheet formula), from your description, it is appears that a simple averaging of those individual percentage values will produce correct result , as the “base” or “reference” for each individual percentage is same. So yes, AVERAGE(SELECT([Percentages], [Dates] = [_THISROW].[Dates])) will work.

Also alternatively, for averaging over the day, in AppSheet table view of these records , you can “group by dates column” and use group aggregate setting of AVERAGE: Percentages where [Percentage] is column for each row’s average entry.

Hope this helps.

(Suvrutt Gurjar) #9

Hi +David Charlon, You may wish to explore following

For aggregating data in charts, you may wish to explore Histogram type charts to aggregate a table column data (Say Expenses, daily sales etc ).

You may wish to take a look at the following sample app and the histogram chart titled "By Month"on Leads table.

Hope this helps.

appsheet.com - Lead Tracking2 - An app to manage and update sales leads. Cool features include dashboard view, charts, status indicator, actions, and workflows. Lead Tracking2 - An app to manage and update sales leads. Cool features include dashboard view, charts, status indicator, actions, and workflows. appsheet.com