3 Replies Latest reply: Aug 14, 2015 6:14 AM by neetha P RSS

    Previous Month

    SENTHIL KUMAR

      Hi

       

      Below is my caluclated Dimension which works for current month.

       

      =If(aggr(Avg(TOTAL <Name> Aggr((Count( DISTINCT{$<Action_Date = {"$(='>=' & Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD') & '<=' & Date(Max(ALT1), 'YYYY-MM-DD'))"},AACTION = {'OK','HOLD','CANCEL','OK-MTS'}>} Message)),Name,Attendance_Message)), Name)<600,'0-600',

      If(aggr(Avg(TOTAL <Name> Aggr((Count( DISTINCT{$<Action_Date = {"$(='>=' & Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD') & '<=' & Date(Max(ALT1), 'YYYY-MM-DD'))"},AACTION = {'OK','HOLD','CANCEL','OK-MTS'}>} Message)),Name,Attendance_Message)), Name)<800,'600-800',

      If(aggr(Avg(TOTAL <Name> Aggr((Count( DISTINCT{$<Action_Date = {"$(='>=' & Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD') & '<=' & Date(Max(ALT1), 'YYYY-MM-DD'))"},AACTION = {'OK','HOLD','CANCEL','OK-MTS'}>} Message)),Name,Attendance_Message)), Name)<1000,'800-1000',

      If(aggr(Avg(TOTAL <Name> Aggr((Count( DISTINCT{$<Action_Date = {"$(='>=' & Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD') & '<=' & Date(Max(ALT1), 'YYYY-MM-DD'))"},AACTION = {'OK','HOLD','CANCEL','OK-MTS'}>} Message)),Name,Attendance_Message)), Name)<1500,'1000-1500',

      ''))))

       

       

      If i want to do it for Previous month , how can i do that

       

       

       

      Regards

        • Re: Previous Month
          Onno van Knotsenburg

          This is one hell of a calculated dimension.

           

          Are you sure you can not do this in script instead? Calculated dimensions are not good for performance. Setting a flag in script might make this even a lot easier to do.

          • Re: Previous Month
            Rudolf Linder

            add the function addmonths around your current month definition

            now. Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD'

            new for previous month: addmonths(Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD',-1)

            or use inner Definition Date(addmonths(MonthStart(Max(ALT1)),-1), 'YYYY-MM-DD'

            • Re: Previous Month
              neetha P

              maybe try like this please:

               

              =If(aggr(Avg(TOTAL <Name> Aggr((Count( DISTINCT{$<Action_Date = {"$(='>=' & Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD')-1 & '<=' & Date(Max(ALT1), 'YYYY-MM-DD')-1)"},AACTION = {'OK','HOLD','CANCEL','OK-MTS'}>} Message)),Name,Attendance_Message)), Name)<600,'0-600',

              If(aggr(Avg(TOTAL <Name> Aggr((Count( DISTINCT{$<Action_Date = {"$(='>=' & Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD')-1 & '<=' & Date(Max(ALT1), 'YYYY-MM-DD')-1)"},AACTION = {'OK','HOLD','CANCEL','OK-MTS'}>} Message)),Name,Attendance_Message)), Name)<800,'600-800',

              If(aggr(Avg(TOTAL <Name> Aggr((Count( DISTINCT{$<Action_Date = {"$(='>=' & Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD')-1 & '<=' & Date(Max(ALT1), 'YYYY-MM-DD')-1)"},AACTION = {'OK','HOLD','CANCEL','OK-MTS'}>} Message)),Name,Attendance_Message)), Name)<1000,'800-1000',

              If(aggr(Avg(TOTAL <Name> Aggr((Count( DISTINCT{$<Action_Date = {"$(='>=' & Date(MonthStart(Max(ALT1)), 'YYYY-MM-DD')-1 & '<=' & Date(Max(ALT1), 'YYYY-MM-DD')-1)"},AACTION = {'OK','HOLD','CANCEL','OK-MTS'}>} Message)),Name,Attendance_Message)), Name)<1500,'1000-1500',

              ''))))