Hi Team,
We have data similar to the snapshot, where columns B and C represent the last 2 months’ data. Our goal is to calculate rolling totals for the last 3 months, 6 months, and 12 months based on the dates in columns B and C. Can some one assist me with achieving this? Thank you!
Hi @tdr_Dinesh,
There are a few different ways we can go about, lets look at a couple calculated field that can accomplish this, you can then use to create 3 fields, one for each rolling month date range.
Rolling 3 Month -
ifelse(addDateTime(1-extract ('DD', now() - 1)), 'DD', addDateTime(-2, 'MM', now())) <= {date field} AND now() >= {date field}, {value}, NULL)
For the next 2 month options, you would just switch the amount you need to subtract (the 2 from above calculated field) from the months.
For Rolling 6 Months: -5
For Rolling 12 Months: -11
Let me know if you have any additional questions!
Hi @Brett ,
Thank you! I am encountering an error message while creating a calculated field. I have created a sample dashboard in Arena. Below is the link. Could you please take a look and assist me with this issue?
Thank you!
Hello @tdr_Dinesh, I only updated 2 of the fields, but this should give you an idea of what needs to be done. The table will now show the date categories (the only thing missing here is a way to better format the 2 actual month dates). I tried to set them to be dynamic, but you may want to update the format so they don’t show as a string date like this 2024-04-01.
Basically, you need to remove the date filter from the visual. You could use parameter dates and base the calculated fields off those but I built everything related to today with the now() function. Between the Date Categories field and Review Count field, you should get an idea of the calculations you will need to run to make this work.
Hi @DylanM ,
Thank you!
