# Is rolling up summary values based on dates - is there an easier way to do this?

I have an app that needs to roll up different values for a dashboard section.
Fx how much money each individual has logged in the past 3 months:

This month \$ A
Last month \$ B
Two months ago \$ C
Three months ago \$ D

I have this month figured out by having a math column calculating todays Year and today month with the following:

``````Year(Now) * 100 + Month(Now)
``````

and then another column that figures out the date and month for the logged date for a given record

``````Year(Date) * 100 + Month(Date)
``````

Then an IF/Else Column to see when the two field align which is then rolled up in my users tab.

I can easily do the same for the three other months, by just subtracting 1-3 months from Month(Now).

My problem is when it’s comes to Jan, Feb and March and I need to also change the year and add to the month rather than substracting?

I can come up with a solution that requires a lot of math columns and IF/Else columns that checks if the Todays year and month is smaller than the logged date and then substract 1 from the year and add to the month accordingly.

But before embarking on that journey I wonder if there is an easier way around this?

2 Likes

However I for some reason can’t see how I can modify it to work with my Years and Months rather than Months and Days?

A simple way to deal with this would be to adjust your Math formula as follows:

``````Year(Date) * 12
+ Month(Date)
``````

The resultant number won’t be instantly recognisable as the year/month, but it will handle crossing over the year boundaries correctly. Essentially it’s just counting the number of months since year zero.

4 Likes

My formula results in a date. When you place it in a math column, it should ask for two parameters, a Date, which you can replace with Now or any other date column you have…and the Months parameter, which is the number of months you want to add or subtract from that date. You could replace Months in the formula with the actual number of months you want.

My formula could be modified to get the YYYYMM result you want, but that would double the size of the formula. Instead, you could just use a second math column to convert the resulting date to YYYYMM.

Keep in mind that the Excel EDATE formula that’s also mentioned in my linked post may work too. I haven’t tried it as date math outside of native glide hasn’t always worked well in the past.

@Darren_Murphy makes a good point too, and is much simpler. Just calculate a number that represents a year and month. It doesn’t have to be recognizable as a year and month if you are just using it for a relation. My solution is more about adding a number of months to a date and getting a resulting date with the same day number. Probably a little excessive for what you need.

4 Likes

Thats a great solution - thank you

Is there a way to do similar with days.
So to check if something is yesterday or four days ago?

That might be tricky since the number of days in a month can vary. There will always be 12 months in a year, but you will never have a consistent number of days in a month.

Instead, simple math can give you the number of days. just subtract a date from Now in a math column to get the number of days between the two dates. Shoot, the relative time column will do that for you by feeding it a date, but I’m not sure how useful the result would be for your use case.

Probably worth noting that by default that will give you a duration (hh:mm:ss), so to return the number of days you need to wrap it in a round() or trunc() function. eg `Trunc(Now - Date)`

2 Likes

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.