3 Replies Latest reply: Jun 23, 2015 10:18 AM by Srikanth P RSS

    add target sales by salesperson per date with excel

      Hello guys,

      I need to add target amount which is ordered by salesperson and date (Month and Year)

      How can I import this ? My targets are in Excel with

       

      Salesperson, Date,

      Target_Month, Target_Year (  ---> Targets are in Amount)

       

      The other basic table:

      DocNo,

      Salesperson, Date, AccountNo, Amount,.

        • Re: add target sales by salesperson per date with excel
          florentina dogaru

          table:

          load DocNo,Salesperson, Date, AccountNo, Amount,

          MonthStart(Date) as  Target_Month,

          YearStart(Date) as Target_Year

          from sursa.qvd;

           

          Concatenate (table)

          load Salesperson, Date, Target_Month, Target_Year

          FROM

          xxx.xlsx

          (ooxml, no labels, table is Sheet1);

            • Re: add target sales by salesperson per date with excel

              Well, my fault..

              I want to add the targets by month and years per salesperson like

              SalespersonDateMonthStart(Date) as Target_Month ?? in AmountYearStart(Date) as Target_Year ?? in amountHeader 5
              AA01.01.20151001200
              AA02.02.20151001200
              AA03.03.20151001200
              BB01.01.20152002400
              BB02.02.200152002400

               

              at the end,  I want to get this

               

              SalespersonDateMonthYearsum(amount)sum(target_amount)?
              AA01.01.2015January2015130100
              AA02.02.2015February201550100
              AA03.03.2015....70100
              TOTAL250300
                • Re: add target sales by salesperson per date with excel
                  Srikanth P

                  Generally Targets are more likely Monthly or Yearly. In your file have both seems to be both are same. If you have different month & year targets you can load two targets separately in same tables.

                   

                  // First Load the Monthly Targets.

                  MonthTargets:

                  LOAD

                       SalePerson & '|' &MonthStart(Date) AS %Key_TargetMonthly,

                       Target_Month

                  From TargetFile;

                   

                  YearTargets:

                  LOAD

                       SalePerson & '|' &YearStart(Date) AS %Key_TargetYearly,

                       Target_Year

                  From TargetFile;

                   

                  Create the %Key_TargetMonthly & %Key_TargetYearly in you fact table with same functions.


                  Please note that, year targets on Year level & Month target on Month level.