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]);
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
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
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]);
