8 Replies Latest reply: Apr 2, 2015 3:35 AM by Nidhin Nair RSS

    Sum at load (script) level

    Harrison Chisonga

      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

        • Re: Sum at load (script) level
          Manish Kachhia

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

            • Re: Sum at load (script) level
              Harrison Chisonga

              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

              [C:\...Contracts.qvd]

              (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

              [C:\...2014 - 2015 Sales Budget.xlsx]

              (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;

            • Re: Sum at load (script) level
              Jop Moekotte

              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

              [C:\...Contracts.qvd]

              (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

              [C:\...2014 - 2015 Sales Budget.xlsx]

              (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

              • Re: Sum at load (script) level
                Jop Moekotte

                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

                • Re: Sum at load (script) level

                  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