6 Replies Latest reply: Mar 17, 2014 8:32 AM by Paul Edrich RSS

    Monthly to Weekly Targets

    Paul Edrich

      I have a Target table with the fields TargetMonth, TargetYear & TargetValue. I use   TargetYear & '-' & TargetMonth &  01 00:00:000' As TargetDate

       

      I would like to find some way to take the Monthly Target total to a Weekly Value to display the target Value using a WeekCommencing Date within a straight Table.

       

      I want to do this in the load script to have a WeekTarget field.

       

      Completely stuck on where to start with this one and would appreciate a steer.

       

      P

        • Re: Monthly to Weekly Targets
          sai v

          Hi Paul,

           

          There are two ways to handle this situation:

          1) With the help of set analysis.

          2) create a variable in the load script and use the date time functions like Year() or Month() or Week() and use this variable value in your expressions.

           

          Thanks,

          Sai

            • Re: Monthly to Weekly Targets
              Paul Edrich

              Thanks for the reply Sai

               

              I am using Week Commencing dates as the dimension and do have a master calendar,  due to the dimension I need to use I can only see targets for those weeks where the 1st of each month falls. Ideally I would like to create a script which would take the monthly target/days within the month  and apply this to each day of the week / Date. this would then accommodate month ends part through the week.

            • Re: Monthly to Weekly Targets
              Manish Kachhia

              Don't you have Master Calendar in you script?

              This can solver your problem.

              IF possible, create a sample file and let us have it with your required end result.

              • Re: Monthly to Weekly Targets
                Marcus Malinow

                Paul,

                 

                I've done this sort of thing several times and generally take this sort of approach:

                 

                Load your data

                Build a daily calendar - include a field called something like daysinmonth

                Link your data in to the calendar - e.g. each row in your data is linked to a whole months worth of calendar entries

                Then, your daily calculation would be (value * days) / daysinmonth

                • Re: Monthly to Weekly Targets
                  Srikanth P

                  Hi Paul, Firstly, you dont have the Weekly Target values. But you need to show weekly Targets.

                   

                  So you need to break down Monthly Target into Dialy by divide the Target Value with no of days. Try Like below:

                   

                  Create the Calender and Join the target Table on Starting of the Month.

                  • Re: Monthly to Weekly Targets
                    Paul Edrich

                    This is the script I have which I am now attempting to create the null values, however I am creating a synthetic key and also the dates are not working as expected.

                     

                    Is there a simpler way to achieve a Target Value against each date?

                     

                    TempTable_Target:

                         Load

                         BranchCode,

                         TargetDate,

                         Target

                         Resident MonthlyTargets ;

                     

                         MinMaxDate:

                         Load

                         Min(TargetDate) as MinDate,

                         Max(TargetDate) as MaxDate

                         resident TempTable_Target;

                        

                         Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;

                         Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate')    ;

                        

                      Drop Table MinMaxDate;

                     

                      Join (TempTable_Target)

                     

                         Load

                         Date(recno()+$(vMinDate)) as TargetDate

                         Autogenerate vMaxDate - vMinDate;

                     

                    MonthlyTargets:

                        NoConcatenate 

                         Load

                         BranchCode,

                         TargetDate,

                              If( IsNull( Target), Peek( Target ), Target) as Target

                              Resident TempTable_Target

                             

                              Order By BranchCode, TargetDate ;

                         Drop Table TempTable_Target;