Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I've ran into a problem as I've tried putting sales plans into my data load. Currently I am using an AutoNumber function to create a link between actuals and plans.
AutoNumber(StoreId & CalendarDate) as PlanLinkId,
The problem I run into is that not every store will have activity for every CalendarDate. Because of this, the auto generated link is not created and portions of the plans are not able to link. Does anyone have a solution for this?
Thanks,
KL
Hi,
Events:
LOAD
'Event' as Type,
EventId,
EventType,
CalendarDate,
AppointmentId,
QuoteId,
OrderId,
StoreId,
LeadId,
DailyPlanAmount,
LeadSourceId,
CancelSaveAmount,
DailySlotWorth,
CostOfMaterials,
LaborCost,
ServiceRequestId,
ServiceScheduleId,
DailySpendAmount,
SourceId,
SourceBreakdownId,
FixedCostSpendCategory,
LeadHistoryId,
CompHoursUserName,
CompHoursPlanType,
CompHoursWorkedPerDay;
SQL SELECT
EventId,
EventType,
CalendarDate,
AppointmentId,
QuoteId,
OrderId,
StoreId,
LeadId,
DailyPlanAmount,
LeadSourceId,
CancelSaveAmount,
DailySlotWorth,
CostOfMaterials,
LaborCost,
ServiceRequestId,
ServiceScheduleId,
DailySpendAmount,
SourceId,
SourceBreakdownId,
FixedCostSpendCategory,
LeadHistoryId,
CompHoursUserName,
CompHoursPlanType,
CompHoursWorkedPerDay
FROM "EnabledDB_Reporting".Reporting.Events;
Concatenate (Events)
Plans:
LOAD
'Plan' As Type,
StoreId,
CalendarDate,
PlanNetOrders,
PlanWindowUnits,
PlanDoorUnits,
"PlanNetOrders$",
PlanIssuedAppointments,
PlanMarketingSpend
FROM [lib://Desktop/Retailer Plans.xlsx] (ooxml, embedded labels, table is RetailerPlans);
Then it will link to your calendar and you can group with the Store ID, also you have to the type if your need to count Events with plans for example.
Mark
Hello,
One possible way to achieve is to create a list of distinct "StoreID". For a CalendarDate where a Store does not have a record, you can insert a dummy blank record in your actual data set which will not add to your measure value. this way you can achieve all possible associations
If there is no activity, then Sum(Actual) should return zero for that store on the Date with a plan. What do you expect to see instead?
Hi,
Can you not just concatenate the plan data to the Sales data?
Add a field like type to pick them out easily?
Mark
Hello, How would I create a dummy blank record in my actual data set? I do believe that could solve the problem.
How would I concatenate This Plan
Plans:
LOAD
AutoNumber(StoreId & CalendarDate) as PlanLinkId,
PlanNetOrders,
PlanWindowUnits,
PlanDoorUnits,
"PlanNetOrders$",
PlanIssuedAppointments,
PlanMarketingSpend
FROM [lib://Desktop/Retailer Plans.xlsx] (ooxml, embedded labels, table is RetailerPlans);
Into this field
Events:
LOAD
EventId,
EventType, //
CalendarDate,
AppointmentId,
QuoteId,
OrderId,
StoreId,
AutoNumber(StoreId & CalendarDate) as PlanLinkId,
LeadId,
DailyPlanAmount,
LeadSourceId,
CancelSaveAmount,
DailySlotWorth,
CostOfMaterials,
LaborCost,
ServiceRequestId,
ServiceScheduleId,
DailySpendAmount,
SourceId,
SourceBreakdownId,
FixedCostSpendCategory,
LeadHistoryId,
CompHoursUserName, //
CompHoursPlanType, //
CompHoursWorkedPerDay
;
SQL SELECT
EventId,
EventType,
CalendarDate,
AppointmentId,
QuoteId,
OrderId,
StoreId,
LeadId,
DailyPlanAmount,
LeadSourceId,
CancelSaveAmount,
DailySlotWorth,
CostOfMaterials,
LaborCost,
ServiceRequestId,
ServiceScheduleId,
DailySpendAmount,
SourceId,
SourceBreakdownId,
FixedCostSpendCategory,
LeadHistoryId,
CompHoursUserName,
CompHoursPlanType,
CompHoursWorkedPerDay
FROM "EnabledDB_Reporting".Reporting.Events
Thank you!
I think you just need to create a third table that links to the other two tables by PlanLinkId, this table will contain your CalendarDate field then, taken from both other tables (this table could also hold the StoreId taken from both tables). So when you are selecting any CalendarDate / StoreID, all possible records from both tables will be available.
edit:
You can do this using
AutoNumber(StoreId &'|' & CalendarDate) as PlanLinkId,
in both tables,
then
LINK:
LOAD DISTINCT
PlanLinkId,
Subfield(PlanLinkID, '|', 1) as StoreId,
Date#(Subfield(PlanLinkID, '|', 2),'YourDateFormat') as CalendarDate
RESIDENT Plans;
CONCATENATE
LOAD DISTINCT
PlanLinkId,
Subfield(PlanLinkID, '|', 1) as StoreId,
Date#( Subfield(PlanLinkID, '|', 2),'YourDateFormat') as CalendarDate
RESIDENT Events;
DROP FIELD CalendarDate, StoreId FROM Events;
How do you create it so it takes the date and store id from both tables?
Hi,
Events:
LOAD
'Event' as Type,
EventId,
EventType,
CalendarDate,
AppointmentId,
QuoteId,
OrderId,
StoreId,
LeadId,
DailyPlanAmount,
LeadSourceId,
CancelSaveAmount,
DailySlotWorth,
CostOfMaterials,
LaborCost,
ServiceRequestId,
ServiceScheduleId,
DailySpendAmount,
SourceId,
SourceBreakdownId,
FixedCostSpendCategory,
LeadHistoryId,
CompHoursUserName,
CompHoursPlanType,
CompHoursWorkedPerDay;
SQL SELECT
EventId,
EventType,
CalendarDate,
AppointmentId,
QuoteId,
OrderId,
StoreId,
LeadId,
DailyPlanAmount,
LeadSourceId,
CancelSaveAmount,
DailySlotWorth,
CostOfMaterials,
LaborCost,
ServiceRequestId,
ServiceScheduleId,
DailySpendAmount,
SourceId,
SourceBreakdownId,
FixedCostSpendCategory,
LeadHistoryId,
CompHoursUserName,
CompHoursPlanType,
CompHoursWorkedPerDay
FROM "EnabledDB_Reporting".Reporting.Events;
Concatenate (Events)
Plans:
LOAD
'Plan' As Type,
StoreId,
CalendarDate,
PlanNetOrders,
PlanWindowUnits,
PlanDoorUnits,
"PlanNetOrders$",
PlanIssuedAppointments,
PlanMarketingSpend
FROM [lib://Desktop/Retailer Plans.xlsx] (ooxml, embedded labels, table is RetailerPlans);
Then it will link to your calendar and you can group with the Store ID, also you have to the type if your need to count Events with plans for example.
Mark
I've already updated my last post with an example.
You can even load the StoreId and CalendarDate fromy your source tables, then you don't need to parse the Key values.