topic Re: Monthly to Weekly Targets in QlikView

Monthly to Weekly Targets

pauledrich — Fri, 14 Mar 2014 14:40:07 GMT

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.

Re: Monthly to Weekly Targets

Fri, 14 Mar 2014 14:46:46 GMT

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

MK_QSL — Fri, 14 Mar 2014 15:07:50 GMT

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

pauledrich — Fri, 14 Mar 2014 15:53:17 GMT

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

marcus_malinow — Fri, 14 Mar 2014 16:07:01 GMT

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

Sat, 15 Mar 2014 01:36:10 GMT

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

pauledrich — Mon, 17 Mar 2014 12:32:43 GMT

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;