How to get sum on monthly basis for calculations?

I have a dataset that has daily numbers for different brand of hotels. What I need to do is calculate RevPAR and RevPAR index numbers. The way to do this is to take the total sum of hotel revenue and divide it by the total number of rooms for every day over whatever time period I’m considering (in this case monthly). I can’t do this calculation on a daily basis. I need the actual sum over the whole months for those two table values (hotel revenue and number of rooms). I’m not able to figure out how to do this. In the analysis I can aggregate by month on the table visual type and get the monthly sums for whatever I choose as “value”, but I can’t use these numbers for calculations.

Another thing that I hope I can do is get this calculation over the previous 28 days of whatever date we are considering. It would be nice if there was a calculated view on the analysis that would show the RevPAR over how ever many days I needed to look over. Essentially, something that would sum up values over X amount of values and divide the numbers, just based on whatever days we filter over.

Have you tried sumOver?


I tried that but it didn’t seem to accomplish what I wanted. I found that using calculated fields within the analysis allows you to easily get summed values by aggregating the dates by whatever time you want (day, monthy, year, etc.).

The only issue is the rolling 28 days calculations for me right now.

For a rolling 28 days can you do running sum?

In regards to grouping your days to 28 you might look at this post for more information.

Hi @jp207

We have not heard back from you regarding your question. We would still like to help. If we do not hear back in the next 3 days, we will archive the question