Hi There,
I'm working on a dashboard which has a component that looks at tracking our product's performance vs two major competitors. While we already have a view showing current sales and market shares in the most recent month, there is something more important that I'd like to design in the dashboard.
I would like to show the sales and market share of each of the 3 products during the first 18 months of their existence. My issue is that the dimension I use in the chart or table (month-year) is not same for each product.
For example, Product A started in Feb of 2012, Product B entered the market in August of 2013, and Product C entered the market in January of 2014.
What I would like to have is a table showing the sales and market share by month for the first 18 months after launch of each product.
I.e. the first column of Month 1 which is the dimension, would show sales for Product A in Feb-2012, Product B in Aug-2013, and Prod C in Jan-2014. The second column showing Month 2 after launch would show sales for Product A in Mar-2010, Product B in Sep-2013, and Prod C in Feb-2014.
I have figured out a way to show just the sales from each but using a Launch month flag for the minimum date for each product, but I can't use this to aggregate across all products because the flag is only present for data rows with the specific product, and I need it to sum all products in that specific month if I want to calculate market share.
I have attached an excel file of some dummy data and then what I need the end table to look like.
Right now, I have multiple tables layered, but I also want a line graph and I'd prefer not to layer as I then need complicated Max formulas.
The market has many more competitors, so it's more detailed than the sample data, but the idea is to compare the speed of penetration of our new product to similar competitors launches, and see how it's tracking against their historicals.
Any thoughts?
