I have 12 columns with rows of the same amount as a sum, and I need one of these amounts for each month to produce a chart. How is this done? Please click the link below to see how this data looks. Thanks
I’d be happy to help, but I don’t see the link anywhere? @Benjamin_Strickland
What link do you need to see? The second column is linked to the first column four the month of July. The second column sums the first column for all rows.
One way to do it is with a helper table. The high level steps are create a new table with 12 rows… 1 row for each month. Create a relation from the helper table to the dates in your main table. Perform a rollup via that relation.
A good trick is to convert your dates to a number first and then build your relation from those numbers
YEAR(D)*100+MONTH(D)
In a math column Replace D with the date
1 Like
Please click the link below:
Since you have individual month columns in the first table, it will be a little tricky to get all of the values in one column in the second table, but it’s definitely doable. What I think I would do is create a Make Array column in the first table to join all of the monthly Rollup columns into an array. Then in your second table, create a Single Value column to retrieve the array from the first row in the first table. Finally add another Single Value column that points to the first single value column array and returns each item in the array based on the number in you Month Number column. But since arrays are zero based and begin their numbering with zero,you will need to change you month numbering from 1-12 to 0-11 instead.
1 Like