Projected Sales: In the script or the chart?

[erickd1190]

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.

[petter · Accepted solution]

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)

[erickd1190]

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.