3 Replies Latest reply: Apr 16, 2012 7:49 AM by M Paeper RSS

    Aggregating weekly loaded data as monthly, etc

    M Paeper

      Hi all,


      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

        • Re: Aggregating weekly loaded data as monthly, etc
          Stefan Wühl

          According to your definition of assigning CalendarWeekEndingFriDate to months, I think you just need to create the month from these dates. For example, you can create a calculated dimension


          =monthname( CalendarWeekEndingFriDate )


          and then use as expression something like

          = avg(ProductionColumn)


          If this results in the expected values, I would recommend adding a new field that calculates the months from your date field in the load script





          monthname(CalendarWeekEndingFriDate) as YearMonth,



          from ...;


          Hope this helps,



          edit: and to aggregate to year level, just use year() function.

          • Aggregating weekly loaded data as monthly, etc
            jagan mohan rao appala



            Arrive Month and Year dimensions as suggested by Stefan. 


            =monthname( CalendarWeekEndingFriDate )


            Now add this new Month dimension as dimension in chart and use the following expression


            = avg(aggr(Sum(ProductionColumn), CalendarWeekEndingFriDate))


            Hope this helps you.




              • Re: Aggregating weekly loaded data as monthly, etc
                M Paeper

                Awesome, thanks very much Stefan and Jagan,


                Stefan using your suggestion I can do a monthly chart dimension, and you're right Jagan to actually get a value to plot I need to use your aggr formula syntax.


                Given my input data I now get as output this, which is great.


                Hmmm, but now this brings one more question.



                LOAD MonthName(CalendarWeekEndingFriDate) as CalendarMonthYear

                as the scripted dimension


                and then 

                avg(aggr(Sum(CuttingAbsenteeismPercent), CalendarWeekEndingFriDate))

                as the expression I get the above result.


                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?


                Thanks again,




                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.


                Message was edited by: manoangazi