Sales list year over year performance
2 posters
Page 1 of 1
Sales list year over year performance
I'm having trouble coming up with a good way to model what seems like a fairly simple requirement: How is a sales list performing this month versus the same month last year. It sounds simple but there is a twist. If it was just a matter of stamping the sales list on each transaction and summarizing by month, that would be done. The twist is that the sales list comparison has to be made as the list is configured in the reported month and year. When comparing June 2009 to last year (June 2008), the sales list configuration in June 2009 must be used in both time periods. To further complicate the requirement, reports typically show all of the months of the current year, and each month may have a different sales list configuration that must be accurately reported each month and in the prior year for each month.
A sales list is a group of companies that changes (usually slightly) monthly.
The facts are typical sales transactions.
I'm hoping this is common enough that someone has run across it.
A sales list is a group of companies that changes (usually slightly) monthly.
The facts are typical sales transactions.
I'm hoping this is common enough that someone has run across it.
barclaywilliams- Posts : 2
Join date : 2009-07-21
Re: Sales list year over year performance
You need to maintain the historical sales hierarchy that drives the sales list. The hierarcy needs to maintain effective and expiration dates as well as a 'current' flag. Because history is involved, I would usually maintain both a recursive table (to identify changes) and an exploded bridge table, using the latter in reporting. Then it is a simple matter of filtering rows by date against the bridge table... you can easily create a monthly report using different versions of the structure for each month in a single query.
Re: Sales list year over year performance
Thanks for the response -- I think i have that in the kitchen. Maybe my problem is presentation or ease of query. I know that i can write the query that matches up the right hierarchy version to the right time period, but i'm not sure how anyone who would actually use that information would do so.
I'm thinking about two versions of the fact table -- one for current year sales and one for last year sales. the current year table is "normal" and the last year table has all the dates wound forward one year with the territory assignment based on the wound forward date. do you see any pitfalls there? is that just a bad idea?
I'm thinking about two versions of the fact table -- one for current year sales and one for last year sales. the current year table is "normal" and the last year table has all the dates wound forward one year with the territory assignment based on the wound forward date. do you see any pitfalls there? is that just a bad idea?
barclaywilliams- Posts : 2
Join date : 2009-07-21
Re: Sales list year over year performance
Don't create another table. Just define a view that does the joins for the users.
Similar topics
» SSAS 2008R2 - Creating "Year over Year Growth %" or "Year To Date"
» Dimensional modelling for Sales and Marketing (opportunity, quotes, sales orders)
» Sales Rep <--> Customer relationship with Sales Fact Table
» Modeling Invoice Level Sales With a Volatile Sales Org
» Trying to design Sales to Promos to Sales Relationship help Please.
» Dimensional modelling for Sales and Marketing (opportunity, quotes, sales orders)
» Sales Rep <--> Customer relationship with Sales Fact Table
» Modeling Invoice Level Sales With a Volatile Sales Org
» Trying to design Sales to Promos to Sales Relationship help Please.
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|