I am using charts.js to display items over a year, however I can’t figure out how to extract the month data as an expression. What I’d like is to be able to extract out how many times an event occurred by looking up a field called DateOccurred, which is a date, and then extract out how many times that event occurred in a given month. Writing an expression for current date -1 month is not hard, but what that ends up giving me is the date, minus one month (i.e. if it’s the 27th it gives me this month to the 27th prior). What I’d like to be able to do is extract out the month from -1, -2, -3 etc, so we are in April now, I’d liek to extract all events that occurred in the month of March, Feb, Jan etc. Is this possible, or can you only lookup the month-1 from the current date if the data is stored as a date only?
If it isn’t possible, are there any workarounds, like creating a date field, then another field called month and year, so that when the event is created initially, another two fields extract out that data and then the chart looks up the month field and years fields? Any help would be amazing.
Save in database “date” AND “date 2” … second date will be :extracted month from “date” formatted as text (we need this to group all events in April in one group)
Group by Date 2
To display every grouped month in RP //Current cells grouping Date 2//
Hi @exception-rambler , I have used the group by feature in the past, but am finding that with charts.js when I try to round the month, I don’t seem to get results. I have tried a long way around which seems to work, but is quite slow so not really an ideal solution.
So you’re almost there but here’s a few changes that you’ll need to make (or see screenshot below):
Your Starting date should be the lower end of the date range, not the upper. So you should switch your Starting date & Ending date around.
You should then Add a new aggregation and set it to count - this will run a count within each of the groupings created in the first stage of the processing, and attribute it to the grouping.
You’ll then make a reference to that Grouping’s count for the charts values, rather than doing a separate :count. I use Chart.js but not the same plugin as you so the config for that looks a bit different - but the principal holds.
Thanks! I’ve been away so just just got back and tested - that seems to work for my Y value. For my x value, I had been doing each individual month - so like the below. Not sure if there’s a better expression to have just one dynamic expression to cover a year or a set period of time. I’m doing it in a. static way I guess you could say.
This would be possible in a single expression again using a :Group by statement, doing a bogus count or sum, and then referencing the date field and using :format as text… however, :Group by (I have found anyway) can be pretty resource intensive, whilst what you have here is asking v little of your browser. So I think you have your solution
