Multiple group aggregates

[Jayed_Hasan]

I want to show the daily, weekly and monthly sales data such as total number of orders, average order value, total sales amount. This can easily be done if there were options to show multiple group aggregates. But since there's no such option right now I think making virtual columns for each data and showing them in table or chart view can be an option. I'm struggling to make the virtual columns. For example, a virtual column for how many orders are generated based on a particular date. Another column for the sum of all order values on that date and so on.

[WillowMobileSys]

While creating multiple Virtual Columns to show your aggregate values IS an option,  I would only recommend it if you never expect the app to grow - both in implementation complexity and/ or data size.  Having many Virtual Columns, especially those that aggregate over large data tables, will severally harm the Sync performance.  They will be re-calculated on every Sync - even if not needed.

There are two other options:

1)  Create a separate grouped views for each and aggregate value you wish and place them in a dashboard.  Not ideal as the views are not all together to be viewed at a glance and data is replicated.  BUT an advantage is that you can perform filtering to see those individual values in different contexts separately from each other.  Want to see Sales Revenue for the Month of January 2022 - go to that view and apply a filter.

2)  Place the aggregate values into "normal" columns in their own table and perform the calculations as the updates are made.  This requires a lot of actions to make it happen so is a much more complex app BUT you will end up with a much more performant app since the calcs are performed only when needed.  This being a summary table, you can insert rows for different periods - daily, weekly, monthly, quarterly - to allow for filtering to see a summary across all values for particular period of time.

Hope this helps!

[Joseph_Seddik]

When you create a view there IS an option to group by column and to aggregate the group by count, sum, average, minimum and maximum values. 

Also, in general it is better to use slices than virtual columns for that purpose.

[Jayed_Hasan]

Yes, but I can only do sum or count or average, etc. I was trying to show all of these data in one view. I appreciate the solutions here. Initially, I was planning to make different slices and views for showing different data and put all of them in a dashboard. I'll go for that I guess until appsheet brings this feature.

[Joseph_Seddik]

You can still show all this data in one view, but you'll have to create a new table for it especially for that view. 

The new table will have only one editable row. The columns will be: 

• Range column, which can be an Enum with values for examply: Daily, Monthly, Weekly, and All. 
• Start Date and End Date, both with initial calculated values based on the selected Range, and which can also be changed by user.
• Orders, virtual, List base type Ref, pointing  to Orders table with the corresponding SELECT() statement based on the Start Date and End Date.
• Count: COUNT([Orders])
• Sum: SUM([Orders].[order value])
• Average: AVERAGE([Orders].[order value])
• etc.

[Jayed_Hasan]

I'll give this one a try. Thanks for the suggestion.