I'm looking to create a formula that will give me the actual or forecasted cost of a catalog by month, and needs to be able to handle 3 different depending if it is a past month, the current month, or a future month. This will be placed in a pivot table chart that will show catalog cost by month for a year or years. To start off, I have set MM to be equal to the current month in the load script.
Let MM=Peek('FSCLYRMM',0,'CURRENTDAY')
Here's some fields that I have and their definitions (FieldName - Definition). These numbers change and we pull in a 'snapshot' every couple weeks that appends the current data onto the end of existing data. The unique identifier of the 'snapshot' is a field called FileDate which is simply the date of the reload.
WrittenOff - Actualized catalog cost for a past month
TtlCost - The catalogs total cost, the sum of all months that we amortize the cost of the book
PercDone - Forecasted percent of catalogs TotalCost to be written off in a given month
TtlPercDone - Cumulativepercentof catalogs TotalCost written off so far
TrendMonthName - Ties a Month name to the month number through an inline load
TrendFiscalMonth - The month number
TrendDate - Fiscal Month beginning date
Here's an explination of what I want based on month.
Past Month: WrittenOff
Current Month: (TtlCost * TtlPercDone) -WrittenOff <---Only get Written off for past 5 months. Catalogs repeat every 10-14 months
Future Month: TtlCost * PercDone
Here's what I have right now. It seems to be working for past and future months, but not for the current month.
if((sum([WrittenOff]))<>0,sum({<[Trend Month Name]=,[TrendDate]=>}[WrittenOff]) // Take Written off if it exists (past months)
,if([TrendFiscalMonth]=$(MM) and [TrendFiscalYear]=$(YR), sum([TtlCost]*[TtlPercDone]) - sum({<[TrendMonthName]=,[TrendDate]=>}[WrittenOff]) // Current Month
,sum([TtlCost]*[PercDone]))) // future months
I hope this is clear. Thanks a bunch for your help and let me know if you have any questions!
Why don't you compute the rolling Sums in the script and append it to the fact table with a field as 'RollingSum' AS DataType and 'Fact' AS DataType for all the rest
when you want to do the rolling sum, it would simply be SUM({<DataType={RollingSum}>}FieldName) in case you save the costs to the same fieldname as the Fact table.
for performance and optimized speed, remove the set analysis and store the Rolling Sum in a new fieldName.
So ideally, you create a TempTable, and Concatenate the Fact table to that table 3 times, each time, adding the month by 1 and subtracting it by 1 so the data lines up. Now do a SUM() GROUPBY to shrink the table and CONCATENATE it to the FACT table.
Why don't you compute the rolling Sums in the script and append it to the fact table with a field as 'RollingSum' AS DataType and 'Fact' AS DataType for all the rest
when you want to do the rolling sum, it would simply be SUM({<DataType={RollingSum}>}FieldName) in case you save the costs to the same fieldname as the Fact table.
for performance and optimized speed, remove the set analysis and store the Rolling Sum in a new fieldName.
So ideally, you create a TempTable, and Concatenate the Fact table to that table 3 times, each time, adding the month by 1 and subtracting it by 1 so the data lines up. Now do a SUM() GROUPBY to shrink the table and CONCATENATE it to the FACT table.
