Comparing total sales budgets against actual sales.

[#1]

I am trying to created a table thats compares my budgeted sales and margin to the actual sales and margin by YearMonth for branches and product groups.

I have loaded all the data but have found that where there is a budget for either product group or month and there are no sales for the month or product group the budget amount is not included.

I think the issue is from how the tables are linked (the budgets being linked to the sales). But I am unsure how to fix.

What I want to show is the total budget amount regardless of whether there are sales or not.

/******************** Product Budgets *******************/

SalesBudgettemp:

Directory;

CrossTable (YearMonth, SalesBudget, 2)

LOAD *

FROM

[..\Import Files\Branch Budget from Apr 2008.xlsx]

(ooxml, embedded labels, table is NettBudget);

SalesBudget:

Load

Branch &'-'& PRODUCTGROUP &'-'& MonthStart(Date#(YearMonth,'YYYYMM')) AS BranchBudgetKey,

SalesBudget

Resident

SalesBudgettemp;

Drop Table SalesBudgettemp;

GMBudgetTemp:

Directory;

CrossTable (YearMonthGM , GMAmount, 2)

LOAD *

FROM

[..\Import Files\Branch Budget from Apr 2008.xlsx]

(ooxml, embedded labels, table is GM%Budget);

GMBudget:

Load

Branch &'-'& PRODUCTGROUP &'-'& MonthStart(Date#(YearMonthGM,'YYYYMM')) AS BranchBudgetKey,

GMAmount

Resident

GMBudgetTemp;

Drop Table GMBudgetTemp;

/*************** Links to the Sales Details table *****************

SalesLinking:

Left Join (SalesDetails)

load

MasterorderKey,

InvLineNumber,

AUDITCENTRE &'-'& ApplyMap('ProdGroup_Map',STOCKCODE,'MISSING')&'-'& MonthStart(InvoiceDateID) AS BranchBudgetKey,

CustomerGroup &'-'& ApplyMap('ProdGroup_Map',STOCKCODE,'MISSING') &'-'& MonthStart(InvoiceDateID) AS GroupRebateKey,

Resident SalesDetails;

[marcohadiyanto]

hi,

that's same as my problem..

[#3 · Accepted solution]

I have resolved my earlier question regarding missing budget figures.

I used CONCATENATE to load my budget to sales detail and added bracnh / product group and various year month and dateID keys. This is now bring through the complete budget regardless of any product theat do not have sales.

RE: new script below

//********************************* Product Budgets ***********************************

SalesBudgettemp:

Directory;

CrossTable(YearMonth, SalesBudget, 2)

LOAD *

FROM

[..\Import Files\Branch Budget from Apr 2008.xlsx]

(ooxml, embedded labels, table is NettBudget);

SalesBudget:

concatenate (SalesDetails)

Load

Branch &'-'& PRODUCTGROUP &'-'& MonthStart(Date#(YearMonth,'YYYYMM')) AS BranchBudgetKey,

YearMonth,

Date#(YearMonth,'YYYYMM') AS BudgetDateID,

Date#(YearMonth,'YYYYMM') AS YEARMONTHID,

Dual(Month(Date#(YearMonth,'YYYYMM')),Month(addmonths((Date#(YearMonth,'YYYYMM')),-3))) AS MONTHNAMEID,

Date#(left(YearMonth,4),'YYYY') AS YEARID,

YearName((Date#(YearMonth,'YYYYMM')),0,$(vBudStartMonthFY)) AS YEARFYID,

Branch AS AUDITCENTRE,

PRODUCTGROUP AS PRODUCTGROUP,

SalesBudget

Resident SalesBudgettemp;

Drop Table SalesBudgettemp;

GMBudgetTemp:

Directory;

CrossTable(YearMonthGM , GMAmount, 2)

LOAD * FROM

[..\Import Files\Branch Budget from Apr 2008.xlsx]

(ooxml, embedded labels, table is GM%Budget);

GMBudget:

concatenate (SalesDetails)

Load

Branch &'-'& PRODUCTGROUP &'-'& MonthStart(Date#(YearMonthGM,'YYYYMM')) AS BranchBudgetKey,

YearMonthGM,

Date#(YearMonthGM,'YYYYMM') AS BudgetDateID,

Date#(YearMonthGM,'YYYYMM') AS YEARMONTHID,

Dual(Month(Date#(YearMonthGM,'YYYYMM')),Month(addmonths((Date#(YearMonthGM,'YYYYMM')),-3))) AS MONTHNAMEID,

Date#(left(YearMonthGM,4),'YYYY') AS YEARID,

YearName((Date#(YearMonthGM,'YYYYMM')),0,$(vBudStartMonthFY)) AS YEARFYID,

Branch AS AUDITCENTRE,

PRODUCTGROUP AS PRODUCTGROUP,

GMAmount

Resident GMBudgetTemp;

Drop Table GMBudgetTemp;

//********************************* Sales Header **********************************

SalesHeader:

Left Join (SalesDetails)

LOAD

"SAHED_ARMasterFileId" AS ARMASTERID,

if("SAHED_SaleTypeFlag" = 'I' , 'Invoice', 'Credit') AS SalesType,

"SAHED_SaleTypeFlag" AS SalesTypeKey,

"SAHED_AuditCentre" AS AUDITCENTRE,

"SAHED_AuditNo" AS AUDITNO,

"SAHED_SuffixNo" AS SUFFIXNO ,

autonumber("SAHED_ARMasterFileId"&'-'&"SAHED_AuditCentre"&'-'&"SAHED_AuditNo") AS MasterorderKey,

"SAHED_AccountId" AS CustomerCode,

autonumber("SAHED_ARMasterFileId"&'-'&"SAH ED_AccountId") AS MasterAccountKey,

"SAHED_InvoiceDate"

Date#("SAHED_InvoiceDate",'YYYYMMDD') AS InvoiceDateID,

Date#(left("SAHED_InvoiceDate",6),'YYYYMM') AS YEARMONTHID,

Date#(left("SAHED_InvoiceDate",4),'YYYY') AS YEARID,

"SAHED_Salesperson" AS Salesperson,

if("SAHED_ClosedFlag" = 'Y', 'Posted','Not Posted') AS SaleStatus,

"SAHED_PNREF";

SQL SELECT *

FROM "SAHED_Sales_Header"

where left("SAHED_InvoiceDate",4)>=2008 AND "SAHED_ARMasterFileId" ='ARCOY' AND "SAHED_ClosedFlag" <> 'X' AND "SAHED_ClosedFlag" <> 'C';

//************************* Links to the Sales Details table ********************************************

SalesLinking:

Left Join (SalesDetails)

load MasterorderKey,

YearName(InvoiceDateID,0,$(vStartMonthFY)) AS YEARFYID,

Dual(Month(InvoiceDateID),Month(addmonths(InvoiceDateID,-3))) AS MONTHNAMEID

Resident SalesDetails;