I have a sheet containing sales with the name of the product and an amount for each sale. I’d like to display a list of products and the total value of sales for each product in a simple table.
I know that I could create a table in my spreadsheet and do the calculation there, using SUMIFs but I’d prefer not to because a) that’ll use up more rows b) I can get to the outcome that I want using a stack chart, where the legend gives me the table I want c) I’m applying date range filters to the data and it’s easier to do that with the individual transactions list.
Thanks I do have a separate products table. But I also need to be able to filter the sales from my sales table, to only display sales within a particular month & year, would that be possible if I’m using rollups? I’m guessing that all of my sales amounts will be linked to each product and I’ll lose the granularity of also knowing when the sale took place.
Yes, you can do that by using a template to create a dynamic relation. There are a few different ways to approach it. I would probably do something like this:
Create two user specific columns in your Products table
– One to hold a year selection
– One to hold a month selection
Now create two Single Value columns that take each of those and apply to all rows
Create a template column that joins the Product ID/Name, single value year and single value month
Create an identical template column in your Sales table (you may need to use a bit of date math - or the format date plugin - to extract the year and month from your Sales records).
Next, a relation from your Products table to your Sales table that joins those two templates.
And finally, a rollup column as described earlier
Now all you need is two choice components - one for year, one for month - that write to those two user specific columns. Then as you change the year/month values the relations and associated rollups will dynamically update.
Great thank you, I was really hoping that there was another Glide component which replicated the functionality of chart legends, as that’s really all I need here. But if not, I’ll use your solution or stick with the stack chart, since the stack’s not that intrusive.
