February 10, 2009 at 10:29 pm
I have a date dimension which has a calender month hierarchy and a day of week hierarchy. I have a products dimension. I have a sales fact table. Not every product sells on every day.
I need to get the date of the first time each product sold (the day in which it became "active"). I then want to build a set of dates, for each product, from the date they first sold until today. Then, I want to count the number of instances of each day of week. So, if a product has been active for 12 weeks, it might have 12 Mondays, 12 Tuesdays... and 13 Saturdays.
I've been bashing my head against this experimenting with NonEmpty in a calculated member for the first step (getting the first date) but it keeps timing out - even with only one of the products selected. I'm sure it can't be that hard. I'm not going to post my experiments because I don't want to taint any suggestions.
Can someone please point me in the right direction here? :blush:
February 10, 2009 at 10:54 pm
Hey Sam,
Any hints on what you want the output to look like? Based on what you've written, is it really necessary to get the 'active' date if what you're looking for is a sale count per DOW?
I'm guessing it's likely to be like
activedate Mon Tues Wed.....
product1 01/01/09 34 15 0
product2 05/22/09 22 11 99
product3 ........
Steve.
February 10, 2009 at 11:19 pm
Hi Steve,
Had a chat with the BA and turns out we don't need this info anymore anyway! W00t!
Would still be interested to see how it's done if you're interested in answering, but if not don't worry (and thanks :)).
The resultset I needed was along the lines of:
ProductId Mon Tue Wed...Sun
ABC456 10 10 11 11
XYZ987 5 5 6 6
The numbers indicate the count of each day of week for which the product has been active, not the units sold on those days (which I concede, would be easier).
Sam
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply