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.
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
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?
Resident MonthlyTargets ;
Min(TargetDate) as MinDate,
Max(TargetDate) as MaxDate
Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;
Drop Table MinMaxDate;
Date(recno()+$(vMinDate)) as TargetDate
Autogenerate vMaxDate - vMinDate;
If( IsNull( Target), Peek( Target ), Target) as Target
Order By BranchCode, TargetDate ;
Drop Table TempTable_Target;