I have rows with date as one column and sales as another column. I use rollup to get the total of sales column. But, how do I separate the rows based on date and get the total of sales column values only for one date? For example, there are ten rows with one date value in date column, and I want total of all sales values in the ten rows. How do I use rollup in this case?
- Create a template column to “lock” the value of your date. This will contain just a single replacement, which is the date.
- Use that column to create a multi-relation, joining the column to itself (ie. a self-relation)
- Create a rollup column, calculating the sum of amounts through that relation
2 Likes
Thanks! That is an awesome idea! I have implemented it. Now I am thinking of how to display it. I want to display the date and sales in each date. Right now, I have two values A and B for two dates X and Y. For each date X, there is A in the column, and for Y there is B in the column. How to show Date X - A and Date Y - B?
Can you explain a little more or share screenshots of what you have now? Are you saying that your 10 rows only have 2 dates, or they could have any number of dates and you want a get a single cell that lists each unique Date-Sales as a string of text?
Yes, thats right. It may have any number of dates, and I want to get a single cell that lists each unique Date- Sales as a string of text.
First, if you don’t already have a Row ID column, I would add one. Create a Template column joining date and the rollup of sales. Since you already have that self relation, I would add a single value column that gets the first row id from the relation. Then create an If Then column to compare the Row ID column to the Single Value column. If it matches, then return the date/sales template. Else return nothing. Finally create a Joined List column that will join all the values in the If Then column. The final result should be a joined list of unique dates and total sales for each date.
2 Likes
I am having trouble implementing this.
I want to sum the Line Total by Unique ID. That is I want a sum for all of the line totals that have the same unique id.
I have a Unique ID for each line that comes from A Work Orders file, this work fine.
I have a Relation set up on Unique ID that is multi (see pic).
I have a rollup column that sums line total but is bringing back all the line totals, not just the one (see pic).What am i doing wrong. Many thanks …
Your rollup column is pointing to the sheet, so it sums everything in the sheet. You need to point it to the relation so it only sums the data with the same unique id.
WOWZER …
Thank you kind sir.
1 Like