Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Sales list year over year performance

2 posters

Go down

Sales list year over year performance Empty Sales list year over year performance

Post  barclaywilliams 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

Back to top Go down

Sales list year over year performance Empty Re: Sales list year over year performance

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

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

http://aginity.com

Back to top Go down

Sales list year over year performance Empty Re: Sales list year over year performance

Post  barclaywilliams 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

Back to top Go down

Sales list year over year performance Empty Re: Sales list year over year performance

Post  ngalemmo Thu Aug 06, 2009 2:11 pm

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

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

http://aginity.com

Back to top Go down

Sales list year over year performance Empty Re: Sales list year over year performance

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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