Sales list year over year performance

View previous topic View next topic Go down

Sales list year over year performance

Post  barclaywilliams on Tue Aug 04, 2009 2:15 pm

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.

barclaywilliams

Posts : 2
Join date : 2009-07-21

View user profile

Back to top Go down

Re: Sales list year over year performance

Post  ngalemmo on Tue Aug 04, 2009 3:53 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Sales list year over year performance

Post  barclaywilliams on Thu Aug 06, 2009 11:57 am

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?

barclaywilliams

Posts : 2
Join date : 2009-07-21

View user profile

Back to top Go down

Re: Sales list year over year performance

Post  ngalemmo on Thu Aug 06, 2009 2:11 pm

Don't create another table. Just define a view that does the joins for the users.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Sales list year over year performance

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum