How can I calculate a Cost of Goods (COGS) and ultimately a Gross Profit in the load script? I'm thinking I will need several preceding loads. I still want to keep all the data from the this table too.
The CategoryID's that would be included in COGS are Food Cost, Paper Cost, Kids Premiums. This is my current Load Script.
PLCat:
LOAD
StoreID,
CategoryID,
ReportArea,
GroupID,
CategoryName,
FiscalYear,
ReportingPeriod,
CategoryOrder,
Week,
Amount;
SQL SELECT *
FROM Analysis.dbo."vST_PandLCatUnpivoted"
WHERE FiscalYear = 2012;
Ultimately, I would like to be able to show in a straight table Net Sales, Food Cost, Paper Cost, Kids Premiums, COGS, then a line that is Gross Profit, which is Net Sales - COGS. The attached picture is what I have right now.
