Hi Jonathan,
I've attached the QVW as well as the script here:
Main Tab:
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 MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
LET vToDate = Date(YearEnd(Today()), 'YYYYMMDD');
CUSTOM CONNECT TO "Provider=SalesForceDLL.dll;F26;XUserId=eQfMbSFMHDbKGXENODdKWTJOBDdSWXZNAKZCWTRNFDcEXXRMVbYIXUZOJDdSHXMMPLbGA;XPassword=YMeCUCFMHDbKWYIGTBNKTXA;";
Fact:
//Actual Revenue Load
LOAD
Id as FactId,
// Id as RevenueId,
Name as FactName,
// Milestone__c as MilestoneId,
Project__c as ProjectId,
'' as Program,
Client__c as Client,
'' as [Task Value],
'Booked' as FactType,
'' as PFA,
Project_Name__c as ProjectName,
Project_Number__c as PR#,
'' as Active,
Effective_Date__c as [Actual Date],
Amount__c as Amount;
// Amount__c as Revenue,
// '' as Booked,
// '' as SNS,
// '' as Unallocated;
SQL SELECT *
FROM Revenue__c
WHERE Record_Type_Name__c = 'Actual';
//Booked Revenue Load
LOAD
Id as FactId,
// Id as RevenueId,
Name as FactName,
// Milestone__c as MilestoneId,
Project__c as ProjectId,
'' as Program,
Client__c as Client,
'' as [Task Value],
'Booked' as FactType,
'' as PFA,
Project_Name__c as ProjectName,
Project_Number__c as PR#,
'' as Active,
Effective_Date__c as [Actual Date],
Amount__c as Amount;
// '' as Revenue,
// 'Amount__c' as Booked,
// '' as SNS,
// '' as Unallocated;
SQL SELECT *
FROM Revenue__c
WHERE Record_Type_Name__c = 'Forecast'
AND Revenue_Status__c = 'Planned';
//SNS Revenue Load
LOAD
Id as FactId,
// Id as RevenueId,
Name as FactName,
// Milestone__c as MilestoneId,
Project__c as ProjectId,
Client__c as Client,
'' as [Task Value],
'SNS' as FactType,
'' as PFA,
Project_Name__c as ProjectName,
Project_Number__c as PR#,
'' as Program,
'' as Active,
Effective_Date__c as [Actual Date],
Amount__c as Amount;
// '' as Revenue,
// '' as Booked,
// 'Amount__c' as SNS,
// '' as Unallocated;
SQL SELECT *
FROM Revenue__c
WHERE Record_Type_Name__c = 'Forecast'
AND Revenue_Status__c = 'Signed not Scheduled';
LOAD
Id as FactId,
// Id as RevenueId,
Name as FactName,
// Milestone__c as MilestoneId,
Project__c as ProjectId,
Client__c as Client,
'' as [Task Value],
'Unallocated' as FactType,
'' as PFA,
Project_Name__c as ProjectName,
Project_Number__c as PR#,
'' as Program,
'' as Active,
// Effective_Date__c = MAKEDATE(2014,12,31) as [Actual Date],
// '' as [Actual Date],
// IF(Revenue_Status__c = 'Unallocated', Effective_Date__c = Date(YearEnd(Today())),Effective_Date__c) as [Actual Date],
IF(Revenue_Status__c = 'Unallocated', Date(YearEnd(Today()))) as [Actual Date],
Amount__c as Amount;
// '' as Revenue,
// '' as Booked,
// 'Amount__c' as SNS,
// '' as Unallocated;
SQL SELECT *
FROM Revenue__c
WHERE Record_Type_Name__c = 'Forecast'
AND Revenue_Status__c = 'Unallocated';
LOAD
Id AS [FactID],
Id AS ProjectId,
'' as FactType,
Name AS [FactName],
Account_Text__c as Client,
PFA_Text__c as PFA,
// Project_Financial_Analyst__c AS [PFAID],
Project_Number__c AS [PR#],
Name as ProjectName,
pse__Is_Active__c AS [Active],
Program__c as [Program],
'' as Amount,
'' as [Task Value];
// '' as MilestoneId,
// '' as Revenue,
// '' as Booked,
// '' as SNS,
// '' as Unallocated,
// '' as [Actual Date];
SQL SELECT *
FROM pse__Proj__c
WHERE (NOT Name LIKE '%Template%')
AND (NOT Name LIKE '%Test%')
AND
(pse__Project_Type__c = 'Customer Project' OR (pse__Project_Type__c = 'Internal' and Unallocated_Project__c = True)); //Added Internal Projects w/ Unallocated Revenue = True on 6/13/14 by Luke F. to accommodate Mary Trant's request.
Master Calendar Tab:
/*************** MinMax Table *************
Keeps minimum and maximum Date value from Facts table
*/
MinMax:
LOAD
Min([Actual Date]) as MinDate,
Max([Actual Date]) as MaxDate
RESIDENT Fact;
LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));
LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));
LET vToday = $(vMaxDate);
/*************** Temporary Calendar *************
Generates a single table with one field containing
all existing dates between MinDate and MaxDate.
*/
TempCal:
LOAD
date($(vMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
$(vMaxDate) - $(vMinDate) + 1;
DROP TABLE MinMax;
/*************** Master Calendar ***************
Disconnected during the Date Island exercise by renaming TempDate as IslandDate
*/
MasterCalendar:
LOAD
//TempDate as IslandDate,
//Date(TempDate,'MM/DD/YYYY') AS CaseDate,
Date(TempDate) AS [Actual Date],
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
Day(TempDate) AS Day,
Weekday(TempDate) AS WeekDay,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
//Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
//Date(TempDate, 'MMM-YYYY') AS MonthYear,
Month(TempDate)&'-'&Year(TempDate) AS MonthYear,
Year(TempDate)&'-'& num(month(TempDate)) AS YearMonth,
Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
// MonthStart = num(date(MonthStart(AddMonths(Today(),$(MonthsFromToday),'DD/MM/YYYY')))),
inyeartodate(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,
inyeartodate(TempDate, $(vToday), -1) * -1 AS LastYTDFlag
RESIDENT TempCal
ORDER BY TempDate ASC;
DROP TABLE TempCal;
