Summary by Year and month

Glenn_Thorne · 02-26-2021 05:24 PM

Hi All

I have a table called Orders. The Key is order ID.

I have a virtual field call YrMth that holds the year and month for each order Ex. 201912 Year 19 and month 12

I am trying to construct a filtered select statement that allows me to obtain a list of months. i.e [YrMth] where each one is unique. I can then calculate the total sales per month.

Anyone?

Marc_Dillon

The question is not very clear. Are you looking for this?

OptimiX_XcrY

If you are trying what I think you are

Way 1, the basic one :
Have a column in your table that holds the total value of that row.
Create another view of your table, with grouping by YearMonth column,and group aggregate function SUM from the row total column.

Way 2 , for more options , which I ended up with , :
Create another table , with a column YearMonth , and manually populate it with all the values. It’s easy in GSheets.

Use that table for all sorts of summaries , using virtual columns.

MultiTech

This is the way I would go, using references to connect the two tables.

Glenn_Thorne

Thanks for this,

I understand this approach and method. I guess I was just trying to avoid using another table and also didn’t want to have to manually enter [YrMth] values for future months. But I can of course easily filter records in the new table. Thanks again…

MultiTech

I would utilize a background record creation action, put on a schedule. You can make it so that it’s watching to see if there’s a new month or year that hasn’t been created yet, and when a corresponding record that would need that grouping… This action runs and creates that in the background for you.

This way you don’t actually have to go in and maintain anything, it all just kind of builds off of itself.

Glenn_Thorne

Yes, I can create the action to add a row and change the [YrMth] data but I cannot seem to create a way to schedule it?? I tried creating a workflow that would check if a new [YrMth] is needed and then call the add row action every time a record is created but it will not allow me to call the add row action only change data actions???

MultiTech

It’s not a workflow you want to create, it’s a report… You can put those on a schedule. They’re just like a workflow, but they run on schedules.

Alternatively you could use the new automation panels as well

Glenn_Thorne

Ahhh yes I understand but the report cannot trigger the add record action to check and add a new YrMth value… or can it?

Glenn_Thorne

Got it. Have to report by row rather than table in order to acess change data functionality.