Table comparing the first 18 months of product sales for 3 products, each with different introduction dates to the market?

[richard_cioci]

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?

[sunny_talwar]

Is this something you want?

If yes, then try the following script:

Table:

LOAD Date#(Date, 'MMM-YY') as Date,

     [Prod A],

     [Prod B],

     [Prod C],

     [Market Total]

FROM

Communty_168030.xlsx

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Table1:

LOAD RowNo() as TimeLine,

  'Prod A' as ProductType,

  [Prod A] as MT

Resident Table

Where Len(Trim([Prod A])) <> 0;

Table2:

NoConcatenate

LOAD RowNo() as TimeLine,

  'Prod B' as ProductType,

  [Prod B] as MT

Resident Table

Where Len(Trim([Prod B])) <> 0;

Table3:

NoConcatenate

LOAD RowNo() as TimeLine,

  'Prod C' as ProductType,

  [Prod C] as MT

Resident Table

Where Len(Trim([Prod C])) <> 0;

FinalTable:

NoConcatenate

LOAD *

Resident Table1;

Concatenate (FinalTable)

LOAD *

Resident Table2;

Concatenate (FinalTable)

LOAD *

Resident Table3;

DROP Tables Table1, Table2, Table3;

[richard_cioci]

Yes this looks like it will work great. My only question is around the len(trim([Prod C]))<>0, if the sales in a month are 0, will it eliminate the record for this month? even if this is the 5th or 6th month? Cuz I want to ensure I can use this in any case in the future when I might need it as well, and I want to make sure it keeps all month records from that first month onward.

[sunny_talwar]

Hey Richard,

Len(Trim([Prod C])) should ideally not eliminate 0 as 0 should have length 1. But let me know if you find it otherwise when you implement the solution at your end.

Best,

Sunny

[richard_cioci]

Ah yes, got it, I will implement a bit later today and get back to you.

[sunny_talwar]

Sounds like a plan. All the best.

Sunny

[richard_cioci]

Ok, so I realized I may have simplified it a little too much when I posted the sample data, because each of my product sales have sales in different regions, so the rowno function won't work for me as is, so I'm trying to figure out how to bridge the gap from your solution.

Also I need market share as a percentage in the month after launch for each product, so I need to somehow sum the market across products in the months after each product was launched.

I've redone my sample data to properly mimic how I have my data actually staged (realized my products are not in separate columns but are in a regular db format where the different product names are all in one column.

Sorry for the rework.

[sunny_talwar]

Does this make sense?

I summed up the sales for a particular product from all location to get this output. Is this the way you wanted to do it?

Table:

LOAD Date#(Date, 'MMM-YY') as Date,

     City,

     Product,

     Sales

FROM

Community_168030.xlsx

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Join(Table)

LOAD Date,

  Product,

  Sum(Sales) as TotalSales

Resident Table

Group By Date, Product;

Table1:

LOAD RowNo() as TimeLine,

  Product,

  TotalSales as TS

Resident Table

Where Product = 'A';

Table2:

NoConcatenate

LOAD RowNo() as TimeLine,

  Product,

  TotalSales as TS

Resident Table

Where Product = 'B';

Table3:

NoConcatenate

LOAD RowNo() as TimeLine,

  Product,

  TotalSales as TS

Resident Table

Where Product = 'C';

FinalTable:

NoConcatenate

LOAD *

Resident Table1;

Concatenate (FinalTable)

LOAD *

Resident Table2;

Concatenate (FinalTable)

LOAD *

Resident Table3;

DROP Tables Table1, Table2, Table3;

Working on getting the market share part now.

Best,

Sunny

[richard_cioci]

yea that's perfect.

[sunny_talwar · Accepted solution]

This looks good?

Table:

LOAD Date#(Date, 'MMM-YY') as Date,

    City,

    Product,

    Sales

FROM

Community_168030.xlsx

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Join(Table)

LOAD Date,

  Product,

  Sum(Sales) as TotalSales

Resident Table

Group By Date, Product;

Join(Table)

LOAD Date,

  Sum(Sales) as TotalSalesforMonth

Resident Table

Group By Date;

Table1:

LOAD RowNo() as TimeLine,

  Product,

  TotalSales as TS,

TotalSales/TotalSalesforMonth as RelativeSales

Resident Table

Where Product = 'A';

Table2:

NoConcatenate

LOAD RowNo() as TimeLine,

  Product,

  TotalSales as TS,

TotalSales/TotalSalesforMonth as RelativeSales

Resident Table

Where Product = 'B';

Table3:

NoConcatenate

LOAD RowNo() as TimeLine,

  Product,

  TotalSales as TS,

  TotalSales/TotalSalesforMonth as RelativeSales

Resident Table

Where Product = 'C';

FinalTable:

NoConcatenate

LOAD *

Resident Table1;

Concatenate (FinalTable)

LOAD *

Resident Table2;

Concatenate (FinalTable)

LOAD *

Resident Table3;

DROP Tables Table1, Table2, Table3;