topic Re: Calculating Targets for Monthly in QlikView

Calculating Targets for Monthly

Tue, 03 Nov 2015 14:25:26 GMT

Hi All

I have a sales table for different branches and a targets for each branch

targets are weekly for each branch but the Sales is by Monthly

how can i convert the weekly target to monthly

i need to calculate the sales percentage against the target

Please Suggest

below is my Master Calendar

Sales:

LOAD Branch,

Date,

Sales

FROM

\\Target Cal.xls]

(biff, embedded labels, table is [Sales$]);

Temp:

Load

min(Date) as minDate,

max(Date) as maxDate

Resident Sales;

Let vMinDate = Num(Peek('minDate', 0, 'Temp'));

Let vMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(vMinDate) + Iterno()-1 As Num,

Date($(vMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

MasterCalendar:

Load

TempDate AS Date,

NUM(TempDate) AS NumDate,

Week(TempDate) as Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

MonthName(TempDate) As MonthYear,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

If(InMonthToDate(TempDate, '$(vToday)'-1,0),1,0) as CurMTDFlag,

If(InMonthToDate(TempDate, ADDMONTHS('$(vToday)'-1,-1),0),1,0) as LastMTDFlag,

If(InMonthToDate(TempDate, ADDMONTHS('$(vToday)'-1,-2),0),1,0) as PrevMTDFlag,

WeekDay(TempDate) as DEC_WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar

Re: Calculating Targets for Monthly

Gysbert_Wassenaar — Tue, 03 Nov 2015 14:38:23 GMT

Since weeks can start in one month and end in the next it's not always possible to map a week to a month. You will have to decide first how you intend to handle that issue.

Regarding the mixed granularity of the periods... perhaps this blog post helps: Fact Table with Mixed Granularity‌

Re: Calculating Targets for Monthly

Tue, 03 Nov 2015 15:19:31 GMT

HI Gysbert Thank You

is there a way i can do it by weekly calculate the sales percentage against the target

Re: Calculating Targets for Monthly

swuehl — Tue, 03 Nov 2015 15:31:07 GMT

In your excel file, it looks like your sales is recorded on a daily basis. So it should be easy to compare sales and target on a weekly basis. If you want a monthly comparison, you could consider breaking down your targets to daily values, too. Then your facts are of same grain and you can compare on any time scale you like.