I've been going in circles with this for hours now so would appreciate some help.
What are generic methods one can use to aggregate lower timeframe data fields based on time/date dimensions. I have tried set analysis but not coming right. Also functions like aggr, rangesum, rangeavg with count(distinct and am not getting anything workable syntax wise that returns correct values.
In the data below you'll see I have data recorded every Friday (its the % absenteeism in a particular department for the week).
When I plot this data the line is too jiggly so I want to take this data and smooth it by plotting the monthly average instead given that I have the weekly averages available, I was hoping to identify and average the data which belongs to a particular month and then just obtain that value. e.g. below I have highlighed in blue the data I want to average for January, I'd like to average the for Feb, Mar, etc as well and plot as monthly data from this.
For example for the Production column for 01/2012 (MM/YYYY format) I'd expect to Avg(4.45, 2.08, 2.87) and plot that as the Jan value. Similarly for 02/2012 I'd expect to Avg(2.87, 2.68, 2.17, 2.39) and plot that as the Feb value ... etc for the rest of the year's months.
How can I effect this? I've been trying to do this as a chart expression with [CalendarWeekEndingFriDate] as my dimension but I'll try any approach that works. Thanks
As you can see I am getting no dimension summarised values output for April 2012.
Am guessing its because the month is still incomplete / presence of null values, but how can I handle this aspect so that for incomplete periods such as April 2012 now, I can get a "MonthToDate" value to plot?
Stop Press. Ignore the above. It actually works fine - also for April. When I tested with just one ProductionColumn, I'm sure I didnt have any date filters active and it wouldnt show me April data hence I was confused. I just added further production columns to the chart using the same aggr expression and April has appeared, so thanks, everything working 100% now.