Hi All,
I have the below script. Where the resident table is not refreshing when I load the data. I have two tables Opportunity and Win tables. Can anyone help me with this issue? When I pull the data for Won Opportunity (Sum({< [Win Marketing Campaign Flag]={'1'}>}[Converted Win Amount])). I don't seem to get the right data.
Opportunity:
LOAD Id as "Opportunity ID",
AccountId as "Account ID",
OwnerId as "Opportunity Owner ID",
Name as "Opportunity Name",
Lookup('CR', 'IsoCode', [CurrencyIsoCode],'Currency') as "ConversionRate",
Amount,
Amount/(Lookup('CR','IsoCode',[CurrencyIsoCode],'Currency')) as "Converted Amount",
Date(floor(CloseDate)) as "Close Date",
"Type" as "Opportunity Type",
CampaignId as "Opportunity Campaign ID",
Lookup('Lead Campaign Name','Id',[CampaignId],'Campaign') as "Opportunity Campaign",
Lookup('Lead Parent Campaign','Id',[CampaignId],'Campaign') as "Opportunity Parent Campaign",
Lookup('Originating_Group__c','Id',[CampaignId],'Campaign') as "Opportunity Campaign Group",
Lookup('LeadMarketingCampaignFlag','Id',[CampaignId],'Campaign') as "Opportunity Marketing Campaign Flag",
Date(floor(CreatedDate)) as "Opportunity Created Date",
Year(CreatedDate) as "Opportunity Year",
Month(CreatedDate) as "Opportunity Month",
Year([CreatedDate])& Dual('Q' & Ceil(Month([CreatedDate])/3), Ceil(Month([CreatedDate])/3)) as ScorecardQuarter,
Opportunity_Status__c as "Opportunity Status",
Oppty_from_Lead__c as "Opportunity from Lead",
if(Oppty_from_Lead__c>0,'Marketing Sourced', 'Marketing Influenced') as "Mktg Sourced vs Influenced Flag",
floor(CloseDate-CreatedDate) as "Opportunity Age",
Lookup('TempRegion', 'OwnerId', [OwnerId],'Owner') as "Region",
Lookup('TempSubTheater', 'OwnerId', [OwnerId],'Owner') as "Sub Theater",
Lookup('User_Sales_Group__c', 'OwnerId', [OwnerId],'Owner') as "Opportunity Sales Group",
Lookup(' User_Sales_Region__c,', 'OwnerId', [OwnerId],'Owner')as "Opportunity Sales Region",
if(Len(Trim("CampaignId"))>0,1,0) as "Opportunity Campaignflag",
If([Type]<>'Sales Out - from stock','EMEArevenueTypeFlag','Other') AS EMEArevenueType_noSalesOut_Flag,
Include_in_Standard_Global_Pipeline__c
FROM [lib://Salesforce QVD/Opportunity.qvd](qvd) where CloseDate<=332704 and Year(CreatedDate)>2012
and ([Include_in_Standard_Global_Pipeline__c]=1 or Match([Type],'Sales Out - from stock'));
//This is a new table from opportunities, a subset for those won
Win:
Load
"Opportunity ID" as "Win Opportunity ID",
"Opportunity Owner ID" as "Win Opportunity Owner ID",
"Close Date" as "Win Date",
floor("Close Date" - "Opportunity Created Date") as "Win Age",
"Converted Amount" as "Converted Win Amount",
"Opportunity Campaign ID" as "Win Campaign ID",
"Opportunity Campaign Group" as "Win Campaign Group",
"Opportunity Marketing Campaign Flag" as "Win Marketing Campaign Flag",
"Opportunity Type" as "Win Opportunity Type",
"Opportunity from Lead" as "Win Opportunity from Lead",
"Region",
Year([Close Date])& Dual('Q' & Ceil(Month([Close Date])/3), Ceil(Month([Close Date])/3)) as ScorecardQuarter
Resident Opportunity where "Opportunity Status"='Closed - Won' and Year([Close Date])>=2012;
