TM1 Forum

Hi,
We've a requirment which deals in this way:

There are stores which are tagged as Old stores..after an year of their existence...say a store opens on 15-Mar-09, then 15-Mar-10 it becomes an old store...
Say on March 2010, the user wants to compare the sales of the store - TY(This Year) Vs LY(Last Year). But when we compare the sales of the store TY Vs LY, we need to deduct the sales of the store from 1 Mar-10 till 15-Mar-10 since the last year the store was not open during these days. How do we attach this to a dimension since I want this to get applied dynamically ??

Looking for your replies asap...

Thanks,
Deepu.

I guess the simplest way to do this would be via TI. If you have a cube containing the store properties (time opened etc) you could schedule a TI task for start of each day that would identify any store that needs to change parent and do so:

Use a view off a basic cube as the source. Then use the Dimension Element Component Delete function to remove store from new (complete an IF check on the age of the store)

DimensionElementComponentDelete('Store Dimension','Old',store);

Then use the Dimension Component Add formula to then insert the child in to correct parent. (Again a simple IF statement to establish the parent.)

DIMENSIONELEMENTCOMPONENTADD('Store Dimension',Parent,store,1.000000);

I hope that helps,

Jim.

But how do we ensure that the comparison of an old store is appropriately done as mentioned in my earlier post?

Ex:
Store opening date - 15 March 2009
Transition from Old store to new store - 15 March 2010
LY sales Vs TY sales at the end of March -

(Sales of the store from 15 March 2010 till 31 March 2010) Vs (Sales of the store during March 2009 which is again 15 March 2009 till 31 March 2009).

Please note that I've deducted the first 15 days of sales from this year sales so that we can get a correct comparison context. Otherwise I would be comparing the sales of March 2010 (i.e. 31 days of sales) with just 15 days of sales last year. since last year the store was not open for the first 15 days.

Expecting your replies...

Thanks..

You could create multiple reporting hierarchies in your store dimension (one for each month) for "like for like" stores versus "new" stores versus "closed" stores but this would quickly become messy and difficult for users to navigate. I wouldn't recommend this!

The best solution for this common problem in retail reporting is to have an additional dimension in your sales reporting cubes , let's call it "Store Type". This is a very small simple dimension with a total and N elements of "Like for like", "New" and "Closed". ("Like for like" = trading for more then 52 weeks or whatever the business rule for this comparison is...) This allows very easy comparison of sales for total business versus comparable store sales versus new store sales etc. and you can also slice and dice data with this dimension.

To facilitate this you should also have a "store lookup" cube which records the status of each store in each week based on the store opening date attribute and the date intersection of each cell in the cube. As data is loaded to the sales cubes this cube is looked up in order to get the element for the "store type" dimension.

I think that should be clear enough, HTH.

Hi Lotsram,
Thanks for your reply..can you elaborate a bit more, on the store lookup part?

Regards,
Deepu.