Monthly to Weekly Targets

[pauledrich]

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

[#2]

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

[MK_QSL]

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.

[pauledrich]

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.

[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

[#6]

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.

[pauledrich]

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;