September 19, 2005 at 2:40 pm
I want to show "comp store" sales. Comparable sales, or comp store sales are those stores that have been open for a year. For exmple, if a store opened on 3/1/2004, comp store sales for that store would be 0/NULL for any date before 3/1/2005 (oversimplification, but close enough). My Store dimension has a GrandOpenDate member property at the lowest level, but I don't see how to compare it to the current date in question. This has to work even when rolled up (the store opened on 3/1/2004 - Q1, but should not show at a calendar quarter level until 4/1/2005 - Q2, since it was not open the entire quarter). I even have a fun twist - our definition of comp store is actually "if the store was open by the 16th of the month," it is considered a comp store for the entire month.
Yes, I could add a CompStore dimension and add the dimension value on every fact row, but there has to be a better way. In my MDX newbie head, I see it formulated at the lowest level as something like "If the store was open on the equivalent date in the previous year, report sales, otherwise NULL." I now believe that IIF can only compare strings or ints - not dates. I can ceratinly create a member property that is the numeric equivalent of the date (e.g. 20040301). Something like:
IIF(val([Store].CurrentMember.Properties ("Grand Open Date")) <= ParallelPeriod([Time].[Fiscal].[Fiscal Year],1) ,Measures.[Sales], NULL)
And once I get that, now it needs to roll up... Some of you may see where this is going. I am going to want to do comparabel measures for cost, inventory, etc. I could write lots more, but hopefully you get the gist.
Thanks,
Larry
Larry
September 20, 2005 at 7:47 am
I think you need to convert the grand open date to a date value and compare it to the system date. If you have Excel installed on the server you will be able to use some of teh Excel VBA functions to do this. Once you have the formula working at the store level take your calculated member and make it hidden to the user. Then create another measure that aggregates the hidden measure for the descendants of whatever level of the locations/store dimension that is currently selected.
That being said you are going to face several other issues. Most often people will want to do year over year analysis of comp store sales. So you have to have a way to also show LY's non comp sales once a store has become comp. And when the analysis is for a three year trend you need a way to filter out the stores that were not comp from the earliest period in the analysis.
What I have wound up doing is using a "Store Status" dimension to be able to slice the stores based on their current status (good for the common Month to Date and Week to Date analysis). For longer term comp store analysis we create named sets that use a member property called "Comp Date" (in our ETL process we have a User Defined Function that takes the store open date and computes the correct comp date when we update the stores dimension) to create sets of stores. For instance we might have a named set that is the set of stores where comp date is on or before 2/1/2005. Then we create a calculated member to sum that set.
September 21, 2005 at 12:03 am
I'd be tempted to look at doing this in the relational side (ie the DB). i.e. have a table that holds the opening_date for each store, then in your fact view create new field for the additional 'comp' related emasures (eg revenue, cost, etc) and have a simple case statement that checks the txn date and the opening_date and nulls the value (of say revenue) if datediff(yyyy, date1, date2) < 1. Depending on your row counts, this could add some processing time to your cube but will mean that end user querying performance should be as fast as normal as you've no calculations to make at runtime.
I guess what I like about it is that it means that regardless of where your user has filtered time to (e.g. year, week, month etc etc) they will always ge the right result without you having to make complex mdx calcs to determine the correct value.
HTH,
Steve.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply