I have a table which has daily fields and I want to be able to sum up some columns in this table within a certain time interval. For example, lets say I have data from 2020-2024 and I want to sum up the cost column from 2021-01-01 to 2021-06-01. but I want to be able to do this for any date range and be able to sum up the costs between any 2 dates. Please let me know if you require any more information.
Im really stuck trying to figure out where to start. all of the date functions dont look like they do what I need to do.
Do I need to do some scripting for this?
Sounds like you just need to filter the data in the visualization to the date range that you're interested in.
For example, if you create a cross table that summarized all the data the way you want to (ie. Sum([Col A]) as [Sum of Column A]), then filter the table on the date column to the date range you're interested in. The table would show the sum of column A for that date range.
If for some reason you don't want to use the filters you can limit the data in the visualization using the 'Limit data using expression' box in Properties > Data. Then you can link the date range using document properties and an expression like: [DateColumn] > ${DateRangeStart} and [DateColumn] < ${DateRangeEnd}, where DateRangeStart and DateRangeEnd are the document properties you create. You can set the document properties easily in a text area if you want to create a bit of an interface.