2 Replies Latest reply: Mar 29, 2011 10:37 PM by TCPSPLTD RSS

    Comparing total sales budgets against actual sales.

      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;

       

        • Comparing total sales budgets against actual sales.
          MARCO HADIYANTO

          hi,

          that's same as my problem..

           

          • Comparing total sales budgets against actual sales.

            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;