I want to calculate a product with frozen and final calculation based on the product introduction date

[neha12160]

I want to calculate a product with frozen and final calculation based on the product introduction date. When the product introduced, the first 3 months will be considered as the final and the remaining months will be considered as frozen.

How the overall calculation will be calculated. Please give some suggestion. For Eg:

If product p1,p2,p3:

P1 introduced on Jan - then Final Calculation is Jan + Feb + March, Frozen calculation is April to Dec

P2 introduced on Mar - then Final Calculation is March+April+May, Frozen calculation is June to Dec

And also to calculate the overall.

[swuehl · Accepted solution]

Assuming you have a fact table with fields like

Fact:

Product, Date, Amount

Then you can find the introduction date per Product and join back to your Fact table

LEFT JOIN (Fact)

LOAD Product, DATE(MIN(Date)) AS IntroductionDate

RESIDENT Fact

GROUP BY Product;

Then do a RESIDENT LOAD of your Fact table and create the flags:

ResultFact:

LOAD Product, Date, Amount, IntroductionDate,

          If(Date <= AddMonths(IntroductionDate,3), 'Final','Frozen') as PeriodFlag

RESIDENT Fact;

DROP TABLE Fact;

Then, in your charts, you can filter amounts by PeriodFlag:

=Sum({<PeriodFlag = {Final} >}Amount)

[neha12160]

the overall amount should contain both the frozen as well as final calculation for all the products. So How it can be done.

I have calculate and add both final and frozen with the flag as the mentioned for sum for final

[swuehl]

if you are using PeriodFlag as additional dimension with e.g. Product, then

=Sum(Amount)

should return both the correct sums for the periods as well as the total.