How to sum values based on date (daily ,weekly, monthly)


how to sum this data based on date.

I don’t see a Date column in your table?

But, a general approach to summarise data by date is as follows:

  • Use a math column to convert the date to an integer in yyyymmdd format:
Year(Date)*10^4
+Month(Date)*10^2
+Day(Date)
  • Create a multiple relation column that matches the math column with itself.
  • Use a Rollup->Sum via the relation to sum the value/s that you’re interested in.
3 Likes

how do i create a multiple relation that matches the math column with itself.

  • Create a relation column.
  • Select the ‘Multiple’ checkbox
  • Configure it so the value in the math column matches the same math column in the same table. Match it to itself.
3 Likes


here is the date column

here are the values i need to sum.

when i follow the steps it doesn’t solve the problem.

Please be specific. Which column are the values in that you need to sum?

car total and suv total

Okay, so both of those columns are rollup columns.
Do you want to sum the rolled up values, or do you want to sum the original values?

Whatever it is, it’s possible. You’re just not providing enough information for me to be able to help you.

I need to sum the roll up columns

Okay, so very first solution that I offered (math column, self relation, rollup) should do that.
If that didn’t work for you, can you please show me how you configured each of the three columns, and how the result was different from what you expected.


I need the values totalled for the day

Did you create the rollup column that uses the relation as @Darren_Murphy mentioned?

2 Likes

Yes I did. It does work. Great solution.
I need to display the data in a dashbaord view.
would I need to create a second table.
and how do i do it monthly?

To do it monthly, adjust the math formula so that it returns a unique value by month. Either of the following will work:

Year(Date)*12+Month(Date)
Year(Date)*10^4
+Month(Date)*10^2

Side note: You might want to consider giving your columns descriptive names. It’s okay when you only have a handful of columns, but once you have 50 or 100 columns you’ll have a tough time remembering what New Column CA is for :wink:

Noted. I am having trouble displaying the data for daily as it is only showing the first row of the table, not the latest.

I’m not sure what I am missing

What i think is you can use the Single Value Column and choose latest?