I am trying to write a year to date calculation, month todate and week to date. the problem is I am not getting totals when I just add one week it does not roll downward from year todate month to date and week to date. I used the BI wizard for time intelligence.
i do not want a whole lot of columns I just want the week to date to roll to year to date by selection upward. IE.
if only wtd 6 is selected i still want the rollup for year to date and month todate for just that month. Here is the current script. This is in 2k5.
CREATE
MEMBER CURRENTCUBE.[Time].[YTD].[Year to Date] AS "NA",VISIBLE
= 1;// Year to Date
(
[Time].[YTD].[Year to Date],
[Time].[Fiscal Year].[Fiscal Year].
Members,[Time].[Fiscal Month].[Fiscal Month].
Members,[Time].[Fiscal Week].[Fiscal Week].
Members,[Time].[DateDimID].
Members,{
[Measures].[MFGOperMgn],
[Measures].[TotalSalesExp],
[Measures].[GrossRegularsalesCWT],
[Measures].[GrossAddBillSalesCWT],
[Measures].[GrossResalesCWT],
[Measures].[GrossConsignmentSalesCWT],
[Measures].[TotalGrossSalesCWT],
[Measures].[DeliveryCostCWT],
[Measures].[LeakerAllowanceCWT],
[Measures].[OtherOfficeInvoiceAllowanceCWT],
[Measures].[AccrualCostCWT],
[Measures].[BrokerCostCWT],
[Measures].[ClaimsCostCWT],
[Measures].[MarketingTotalCWT],
[Measures].[TotalNetSalesCWT],
[Measures].[ProductDirectCostCWT],
[Measures].[CuringCostCWT],
[Measures].[LaborCostCWT],
[Measures].[MeatCostCWT],
[Measures].[MethodCostCWT],
[Measures].[OtherDirectCostCWT],
[Measures].[PackageCostCWT],
[Measures].[CostAdjustmentCWT],
[Measures].[OperatingMarginCWT],
[Measures].[PackageRebateAllocationCWT],
[Measures].[ChickenTrapAllocationCWT],
[Measures].[HamSplitAllocationCWT],
[Measures].[BooksToBrandAllocationCWT],
[Measures].[CorpPackageAllocCWT],
[Measures].[BaconHedgeCWT],
[Measures].[HamHedgeCWT],
[Measures].[MilitaryAccrualAdjustmentCWT],
[Measures].[BooksToBrandAdjustmentCWT],
[Measures].[OverheadExpensesCWT],
[Measures].[PlantManufacturingOverheadAllocationCWT],
[Measures].[UnallocatedMarketingAllocationCWT],
[Measures].[SGAOverheadAllocationCWT],
[Measures].[CorporateOverheadAllocationCWT],
[Measures].[NetPLCWT],
[Measures].[GrossMarginCWT],
[Measures].[Lbs],
[Measures].[Gross Regular Sales],
[Measures].[Gross Add Bill Sales],
[Measures].[Gross Resales],
[Measures].[Gross Consignment Sales],
[Measures].[Total Gross Sales],
[Measures].[Delivery Cost],
[Measures].[Leaker Allowance],
[Measures].[Other Off Invoice Allowance],
[Measures].[Accrual Cost],
[Measures].[Broker Cost],
[Measures].[Claims Cost],
[Measures].[Marketing Total],
[Measures].[Total Net Sales],
[Measures].[Product Direct Costs],
[Measures].[Curing Cost],
[Measures].[Labor Cost],
[Measures].[Meat Cost],
[Measures].[Method Cost],
[Measures].[Other Direct Cost],
[Measures].[Package Cost],
[Measures].[Cost Adjustment],
[Measures].[Package Rebate Alloc],
[Measures].[Chicken Trap Alloc],
[Measures].[Ham Split Alloc],
[Measures].[Books To Brand Alloc],
[Measures].[Corp Package Alloc],
[Measures].[Bacon Hedge],
[Measures].[Ham Hedge],
[Measures].[Military Accrual Adjustment],
[Measures].[Books To Brand Adjustment],
[Measures].[Operating Margin],
[Measures].[Overhead Expenses],
[Measures].[Plant Mfg Overhead Alloc],
[Measures].[Unallocated Marketing Alloc],
[Measures].[SGA Overhead Alloc],
[Measures].[Corporate Overhead Alloc],
[Measures].[Net PL],
[Measures].[Gross Margin]
}
) =
Aggregate({ [Time].[Year To Date].
DefaultMember } * PeriodsToDate([Time].[1Fiscal WTD Hierarchy].[Fiscal Year],
[Time].[1Fiscal WTD Hierarchy].
CurrentMember)
);
Year to Date Calculation
Cameron D
Jazzy,
Take a look at the white paper on www.obs3.com titled a Different Approach to time calculations..
It mayhave what your looking for
Ayooya
Thanks I apprecaiate the document. I will read it thoroughly and see if I can get it to do what I would like it to do.
Thanks,
Scott