Is it possible to have last 12 months in charts and automatically update them?

[Aleksandra]

Hello,

Does anyone know if it is possible to create a chart, so it shows a period by month for the last 12 months? So, for example, if I have data from 2020-2022, I want to be able to see data on my chart from November 2021 to November 2022. On the 1st of December, I would like to see data from December 2021 to December 2022 and so on. So, I would like to see data for the last 12 months updated automatically for each month. Is it possible to do this in Caspio and if it is, what would be the best way to do it?

Also, there is an option for yearly records to roll out by the current year, but it shows data for the current calendar year (2022). I would prefer my yearly data to show values from the last 12 months, not a particular calendar year. Is there a way to make this work as well?

So, I need data rolled out by months and by years for 2 separated charts. Thank you all in advance.

Regards,

Aleksandra

[NiceDuck]

Hello @Aleksandra,

While I would still need more details to be sure, the concept you have mentioned seems attainable.

In regards to the filtering the last 12 month of records, what I would do there is create a task that will run daily which checks the date and mark those records that are from 12 months before and less, then, we can simply apply filters on view or datapage search function to only include marked records.

In my opinion, we will really need to use a task on this since we cannot use dynamic or live dates on formula fields.

[Aleksandra]

Thanks for the response @NiceDuck. However, I would need these filters to be applied to the chart. Is that option available for charts as well or for tabular reports only?

[NiceDuck]

It should be possible, I think? 

The values on the charts still relies on the filtering on the search form. As mentioned, we will use the task to mark records that are 12 months and below and use the datapage filtering to filter out unmarked records. 

[Aleksandra]

Ok, I understand the idea now and will try this approach. Thanks.

[NiceDuck]

I just got another idea from my colleague. 

You may also try having a formula field with a dateadd function. Basically, get the date value on your date field and add 12 months to it. 

Then, on your datapage, just filter the records using the formula field set to capture all records that are greater than and equal today.

So if the date is about 9 months ago, plus 12 months to it, then it would be about 3 months in advance the current date. Since the filter capture every records that are greater than today, then they will be captured.

[Aleksandra]

Thanks a lot. I will experiment with those solutions and see what would be the best fit.