Sum at load (script) level

[chiso_chiso]

Gurus,

I have an excel document with MONTHLY TARGETS per CHANNEL per PRODUCT, a qvd with DAILY SALES numbers per CHANNEL per PRODUCT. I have loaded separately by renaming some of the fields to avoid SYNC Key. I have a calendar script separate. For some reason my associations are not working as I am getting higher budget numbers upon month selection.

Kindly assist

[MK_QSL]

Either provide sample data or your script or sample application...Thanks

[chiso_chiso]

Below are my scripts;

Sales:

LOAD

     ContractNumber,

     Status as [Contracted Status],

     StatusDate,

     DateOfSale,

     Date(Floor([DateOfSale]))  as [Sales_Date],

     Time(Frac([DateOfSale]))   as [Sale Created Time],

     Week([DateOfSale])         as Week,//[Sale Created Week],

     Month([DateOfSale]) as Month,

     Year([DateOfSale]) as Year,

     BusinessUnitCode,

     Channel,

     Product

FROM

(qvd);

Budget201415:

LOAD BusUnitCode,

     Product as ProductType,

     Channel,

     Financial_Year,

     BudgetDate,

     Date(Floor([BudgetDate]))  as NewDate,

     Month([BudgetDate]) as Month,

     Year([BudgetDate]) as Year,

     Budget_Vol

FROM

(ooxml, embedded labels, table is Sheet1);

//FiscalCalendar:

UNQUALIFY Sales_Date, NewDate;

SET vFiscalYearStartMonth = 7; 

LET vStartDate = Num(YearStart(Today(), -1)); 

LET vEndDate = Num(YearEnd(Today())); 

FiscalCalendar: 

LOAD 

*,

Date as Sales_Date,

Date as NewDate, 

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter 

Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name 

LOAD 

*, 

Year(Date) AS Year, // Standard Calendar Year 

Month(Date) AS Month, // Standard Calendar Month

Day(Date) as Day, 

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal Calendar Month 

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear,  // Fiscal Calendar Year

'Week'&Ceil(Day(Date)/7) as  MonthlyWeek;

LOAD 

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date, 

RangeSum(Peek('RowNum'), 1) AS RowNum 

AutoGenerate vEndDate - vStartDate + 1;

drop field Sales_Date from FiscalCalendar;

[jopmoekotte]

Hello Harrison,

What you should do is create a key field to couple your budget with your sales, so that specific budgets are linked with respective sales of a channel, product and timeframe. This should be done on the smallest possible timeframe, which is month (since you have monthly targets, and you want all sales in a month to correspond to that budget).

The calendar you built also links to multiple fields, I suggest you link the Calendar to sales date. This way you can click any date/month/year etc. and get the corresponding sales of that date, which links to the corresponding budget(s).

Something as follows would suffice:

Sales:

LOAD *,

     Product &'_'& Channel &'_'& Sales_year &'_'& Sales_month as Key

;

LOAD

     ContractNumber,

     Status as [Contracted Status],

     StatusDate,

     DateOfSale,

     Date(Floor([DateOfSale]))  as [Sales_Date],

     Time(Frac([DateOfSale]))   as [Sale Created Time],

     Week([DateOfSale])         as Week,//[Sale Created Week],

     Month([DateOfSale]) as Sales_month,

     Year([DateOfSale]) as Sales_year,

     BusinessUnitCode,

     Channel,

     Product

FROM

(qvd);

Budget201415:

LOAD *,

     ProductType &'_'& Budget_Channel &'_'& Budget_year&'_'& Budget_month as Key

;

LOAD

     BusUnitCode,

     Product as ProductType,

     Channel     as Budget_Channel,

     Financial_Year,

     BudgetDate,

     Date(Floor([BudgetDate]))  as NewDate,

     Month([BudgetDate]) as Budget_month,

     Year([BudgetDate]) as Budget_year,

     Budget_Vol

FROM

(ooxml, embedded labels, table is Sheet1);

//FiscalCalendar:

//UNQUALIFY Sales_Date, NewDate;

//SET vFiscalYearStartMonth = 7;

LET vStartDate = Num(YearStart(Today(), -1));

LET vEndDate = Num(YearEnd(Today()));

FiscalCalendar:

LOAD *,

Date as Sales_Date,

//Date as NewDate,

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

;

LOAD *,

Year(Date) AS Year, // Standard Calendar Year

Month(Date) AS Month, // Standard Calendar Month

Day(Date) as Day,

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal Calendar Month

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear,  // Fiscal Calendar Year

'Week'&Ceil(Day(Date)/7) as  MonthlyWeek

;

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

//drop field Sales_Date from FiscalCalendar;

Hope this helps.

Kind regards,

Jop

[chiso_chiso]

Thanks JopMoekotte

I have applied this solution as is and the result are 2 syn keys as per attached. The other thing on selecting a month is only returning my sales values for that month and not on Budget values.

[jopmoekotte · Accepted solution]

Hey Harrison,

You should rename the field BusinessUnitCode in either table so they don't link on the field.

In my example I named the field 'BusinessUnitCode' in 1 table and 'BusUnitCode' in the other.

Then you should do the same for month and year, as you can see in my example, I renamed the Month and Year in the sales table to 'Sales_month' and 'Sales_year'.

You want to link the Budget and Contracts on Product, Channel, year and month (that is what the field Key is for).

All other fields should have a name that are not similar between the tables.

You want to link the Contracts and FiscalCalendar on Sales_Date, so rename the month and year in the Sales table. For example to: Sales_month and Sales_year.

Kind regards,

Jop

[chiso_chiso]

Thanks JopMoekotte, so far so good. I have put in two table objects one for  Budget all fields and other for Sales all fields. On Clear all, both tables get populated, however when I make selections, from a selection list of products only one table gets populated.

How could that be?

[chiso_chiso]

Thanks JopMoekotte I got it

[#9]

Hi Harrison,

Its better you keep one table for budget and sales and link it to the calendar using a comman date field.

Simple star schema approarch where  have one fact table and multiple dimension tables.

Regards

Nidhin