8 Replies Latest reply on Aug 23, 2016 6:54 AM by chris.moore.11

# Calculate market share over time using LOD and quick filter

Hi,

This is my first post in the forum. I'm struggling to calculate market share over time using LOD.

This table provides example data.

Market 1

ProductJan 2015Feb 2015Mar 2015Apr 2015May 2015Jun 2015Jul 2015Aug 2015
A22212111
B11111232
C22123111
D31131232
E22212421
Total10878910107

What I want is to calculate the market share for e.g. Product A for every month using LOD and being able to apply quick filter without recalculations.

So for Product A in Jan 2015 that would be: 2/10 = 20% share of market regardless of what products are visualized,

Feb 2015 that would be: 2/8 = 25% share of market regardless of what products are visualized.

If I would filter out e.g. Product C, I would still want the calculations to calculate based on 10 for Jan 2015 and based on 8 for Feb 2015.

I hope this makes sense and that someone can guide me through this.

Thanks!

Meyser

• ###### 1. Re: Calculate market share over time using LOD and quick filter

Hi, would this work? You would have to create a calculated field for each month because of the way your data is set up (with a field for each month).

• ###### 2. Re: Calculate market share over time using LOD and quick filter

What data source are you using? If it is Excel, text files or google sheets you can pivot the fields from columns to rows. That way only 1 calculated field would serve your need. Take a look at this:

Pivot Data from Columns to Rows

Additionally, if it is not one of those data sources, you can write a custom SQL query so your data is shaped the way it is easily consumable within Tableau. Taller data structure is what you need and wider data structure is what you have. So reshaped data would work best!

Hope this helps!

1 of 1 people found this helpful
• ###### 3. Re: Calculate market share over time using LOD and quick filter

It's very simple.

Once you drag January on text card, click on dropdown and set table calculation Total Percentage. Calculation using Table(Down).

1 of 1 people found this helpful
• ###### 4. Re: Calculate market share over time using LOD and quick filter

What I forgot to mention is that I have discrete date dimension set to months. Is there a way to do it without calculation for each month?

Meyser

• ###### 5. Re: Calculate market share over time using LOD and quick filter

Is the same method.

You drag the month dimension in columns, drag the value and set percentage table down.

• ###### 6. Re: Calculate market share over time using LOD and quick filter

I'm totally in on how to set percent of total using the quick calculations. However, I want to be able to filter out one or several of the products without changing the total for that market.

I have worked around this by using lookup(attr......) as a filter instead of using a quick filter, but I'm guessing that this could be solved using LOD expressions. Why I want a better solution than the lookup function is that I cannot use the calculated filter (lookup(attr....)) for any other viz. Hence, for my specific dashboard I'm inclined to use several product filters instead of one across several vizzes.

Hope this makes more sense.

• ###### 7. Re: Calculate market share over time using LOD and quick filter

In this way we could solve your issues faster.

I used your data and pivoted it.

Market 1

Product

Jan 2015

Feb 2015

Mar 2015

Apr 2015

May 2015

Jun 2015

Jul 2015

Aug 2015

A22212111
B11111232
C22123111
D31131232
E22212421
Total10878910107

See twbx file.

• ###### 8. Re: Calculate market share over time using LOD and quick filter

If you mean you already have your data pivoted as Pooja suggested, you would just add the month field to the lod calculation.