Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm pulling my data from Salesforce.com. I'm trying to assign a date to specific records (Unallocated Revenues) when I load them. The date field on the record is 'Effective_Date__c' (and due to business rules, these specific records do not have an Effective Date so I'm trying to assign one just to classify them somewhere). I'm trying to give them an Effective Date = the last day of the current year.
Below is my load statement:
LOAD
Id as FactId,
Name as FactName,
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,
IF(Revenue_Status__c = 'Unallocated', Effective_Date__c = Date(YearEnd(Today()))) as [Actual Date],
Amount__c as Amount;
When I reload, the Unallocated records display under December 1899.
Is there a way that I can load these records so the Effective Date = December 31, 2014?
Thanks,
Luke
Try just this.
IF(Revenue_Status__c = 'Unallocated', Date(YearEnd(Today())) as [Actual Date],
I was thinking you may want to assign the original date however if its not unallocated:
IF(Revenue_Status__c = 'Unallocated', Date(YearEnd(Today()),Effective_Date__c) as [Actual Date],
Hi Jonathan,
Thank you for your quick reply. I have added this to my IF statement and have received the same output.
Thanks,
Luke
hmm.. i missed an extra ')' to close the IF. Try it once more
IF(Revenue_Status__c = 'Unallocated', Date(YearEnd(Today()))) as [Actual Date],
No worries. I updated and now the Unallocated is not being bucketed by any date. I'm curious as to why previous is bucketing it under Year = 1899?
Thanks,
Luke
if it encounters a 'bad date' value is reverts it to that 1899 date. If it evaluates to NULL you'll get the '-'.
Can you paste in the full script ? I've tested this a number of times and can't think why the logic isn't working. That or a QVW upload/attachment.
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;
i think your load is fine, because if you look at below, even though 'Year' is greyed out, it is actually associating to 12/31/2014 actual date. In the list box next to it that says 2014 at the top, the expression is year([Actual Date]) . I used the same expression to replace Year in the pivot table and it works there too.
so what is wrong ?
I think it may be your master calendar load:
try replacing this:
Date(TempDate) AS [Actual Date],
Week(TempDate) AS Week,
Year(TempDate) AS Year,
Month(TempDate) AS Month,
with
Date(TempDate) AS [Actual Date],
Week(Date(TempDate)) AS Week,
Year(Date(TempDate)) AS Year,
Month(Date(TempDate)) AS Month,
etc... just ot make sure it know TempDate is in fact a Date.