2 Replies Latest reply: Feb 27, 2017 11:07 AM by Erick Dameron RSS

    Projected Sales: In the script or the chart?

    Erick Dameron

      Hi All!

       

      I've run into a wall with this one and being new I thought I would come to the experts for some advice!

       

      I am working with some raw data, pulled using a web connector.

       

      We already have a current sales vs target chart which was fairly easy, just added departments as a dimension and Sum(sales) and Sum(Target) as Expressions.

      I need to create a bar chart that shows the projected sales by department VS target.

      So the formula should look similar to this:

      Sum(Sales)/workdays*Totaldays

      But when I try to type this out in the Chart Expression box I keep getting null values or errors.


      Should I try and create a Projected Sales field within the Load Editor and how would I start?


      Or is this something that with a little push in the right direction I can do in the Chart?


      Here is a look at my data model:

      // Begin SET

      SET ThousandSep=',';

      SET DecimalSep='.';

      SET MoneyThousandSep=',';

      SET MoneyDecimalSep='.';

      SET MoneyFormat='$#,##0.00;($#,##0.00)';

      SET TimeFormat='h:mm:ss TT';

      SET DateFormat='M/D/YYYY';

      SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

      SET CollationLocale='en-US';

      SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

      SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

      SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

      SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

       

       

       

       

      SET vFM = 10; //First Month of Fiscal Year(Oct)

      SET vFD = 6; //First day of the week (Sun)

       

      //Begin Sales1 Table

      LOAD

          Saledate,

          Saleid,

          client_id,

          Product,

          staffid,

          Vendor,

          Acc_code,

          Department,

          Signed_date,

          "rate",

          emp_status

      FROM [lib://Q-SALES ]

      (XmlSimple, table is [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table]);

       

      //Begin Sales2 Table

      LOAD

          Saledate,

          Saleid,

          client_id,

          Product,

          staffid,

          Vendor,

          Acc_code,

          Department,

          Signed_date,

          "rate",

          emp_status

      FROM [lib://Q-SALES ]

      (XmlSimple, table is [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table]);

       

      //Begin Sales3 Table

      LOAD

          Saledate,

          Saleid,

          client_id,

          Product,

          staffid,

          Vendor,

          Acc_code,

          Department,

          Signed_date,

          "rate",

          emp_status

      FROM [lib://Q-SALES ]

      (XmlSimple, table is [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table]);

       

      // Master Calendar for fiscal year

       

      Calendar:

      Load Dual(fYear-1 &'/'& fYear, fYear) AS FYear,

        Dual(Month, fMonth) AS FMonth,

          DUAL ('Quarter' & Ceil(fMonth/3), Ceil(fMonth/3)) AS FQuarter,

          *;

        

      Load Year + IF(Month>=$(vFM), 1,0) As fYear,

        Mod(Month-$(vFM),12)+1 As fMonth,

          DUAL('Quarter' & Ceil(Month/3), Ceil(Month/3)) AS Quarter,

         *;

      Load visitdate,

      Year(visitdate) As Year,

      Month(visitdate) as Month,

      Week(visitdate) as Week

       

      Resident CCM;

       

      // Pulls Emp-Incentive-Link File

      LOAD

          "Employee ID" AS staffid,

          "Last Name First",

          EmployeeStatus,

          Site,

          "Last Hire Date",

          Department,

          "Position Description",

          "Department-Position Description",

          "Supervisor Name",

          CorporateLevelCode,

          "Termination Date",

          PT,

          IP

      FROM [lib://IT Drive- Reporting

      (ooxml, embedded labels, table is [Emp-Incentive-Link]);

       



      Any wisdom, as always is WELCOME!



      Thanks.

        • Re: Projected Sales: In the script or the chart?
          Petter Skjolden

          If you have a table structure that supports it your expression could work.

           

          But as you haven't told us the structure of your table(s) and whether the chart has one and only one dimension - like just deparment and not a time-related dimension - it is really hard to give you a definite answer.

           

          Assuming you have a calendar table like this that has a key named Date which is both in CALENDAR and SALES

           

          CALENDAR:

          Date, Year, Month, Workdays, Days

          2/1/2017, 2017, 2, 1, 1

          2/2/2017, 2017, 2, 1, 1

          2/3/2017, 2017, 2, 1, 1

          2/4/2017, 2017, 2, 0, 1

          2/5/2017, 2017, 2, 0, 1

          2/6/2017, 2017, 2, 1, 1

          2/7/2017, 2017, 2, 1, 1

          2/8/2017, 2017, 2, 1, 1

          2/9/2017, 2017, 2, 1, 1

          2/10/2017, 2017, 2, 1, 1

          2/11/2017, 2017, 2, 0, 1

          2/12/2017, 2017, 2, 0, 1

          2/13/2017, 2017, 2, 1, 1

          .....

          .....

           

          SALES:

           

          Date, sales, Target

          2/1/2017, 4800, 5000

          2/2/2012, 5300, 5000

          .....

           

          The an expression like this should work:

           

          Sum(Sales)/Sum(TOTAL Workdays)*Sum(TOTAL Days)

            • Re: Projected Sales: In the script or the chart?
              Erick Dameron

              Hi Peter,

               

              Thanks for your feedback. My calendar doesn't currently count the number of days (Or working days) but I had planned on adding that in this week. What would be the best way to generate future dates? I would only really need to finish out the current month, but it would be nice if I could do the entire year.

               

              Here is a look at my data model:

              // Begin SET

              SET ThousandSep=',';

              SET DecimalSep='.';

              SET MoneyThousandSep=',';

              SET MoneyDecimalSep='.';

              SET MoneyFormat='$#,##0.00;($#,##0.00)';

              SET TimeFormat='h:mm:ss TT';

              SET DateFormat='M/D/YYYY';

              SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

              SET CollationLocale='en-US';

              SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

              SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

              SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

              SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

               

               

               

               

              SET vFM = 10; //First Month of Fiscal Year(Oct)

              SET vFD = 6; //First day of the week (Sun)

               

              //Begin Sales1 Table

              LOAD

                  Saledate,

                  Saleid,

                  client_id,

                  Product,

                  staffid,

                  Vendor,

                  Acc_code,

                  Department,

                  Signed_date,

                  "rate",

                  emp_status

              FROM [lib://Q-SALES ]

              (XmlSimple, table is [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table]);

               

              //Begin Sales2 Table

              LOAD

                  Saledate,

                  Saleid,

                  client_id,

                  Product,

                  staffid,

                  Vendor,

                  Acc_code,

                  Department,

                  Signed_date,

                  "rate",

                  emp_status

              FROM [lib://Q-SALES ]

              (XmlSimple, table is [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table]);

               

              //Begin Sales3 Table

              LOAD

                  Saledate,

                  Saleid,

                  client_id,

                  Product,

                  staffid,

                  Vendor,

                  Acc_code,

                  Department,

                  Signed_date,

                  "rate",

                  emp_status

              FROM [lib://Q-SALES ]

              (XmlSimple, table is [Envelope/Body/ExportDataSetResponse/ExportDataSetResult/diffgram/NewDataSet/Table]);

               

              // Master Calendar for fiscal year

               

              Calendar:

              Load Dual(fYear-1 &'/'& fYear, fYear) AS FYear,

                Dual(Month, fMonth) AS FMonth,

                  DUAL ('Quarter' & Ceil(fMonth/3), Ceil(fMonth/3)) AS FQuarter,

                  *;

                

              Load Year + IF(Month>=$(vFM), 1,0) As fYear,

                Mod(Month-$(vFM),12)+1 As fMonth,

                  DUAL('Quarter' & Ceil(Month/3), Ceil(Month/3)) AS Quarter,

                 *;

              Load visitdate,

              Year(visitdate) As Year,

              Month(visitdate) as Month,

              Week(visitdate) as Week

               

              Resident CCM;

               

              // Pulls Emp-Incentive-Link File

              LOAD

                  "Employee ID" AS staffid,

                  "Last Name First",

                  EmployeeStatus,

                  Site,

                  "Last Hire Date",

                  Department,

                  "Position Description",

                  "Department-Position Description",

                  "Supervisor Name",

                  CorporateLevelCode,

                  "Termination Date",

                  PT,

                  IP

              FROM [lib://IT Drive- Reporting

              (ooxml, embedded labels, table is [Emp-Incentive-Link]);

               

               

               

              Looking forward to hearing from you.