1 2 Previous Next 17 Replies Latest reply on Aug 3, 2016 8:23 AM by Hildebrando Souza Jr

# KPIs and Trend Lines on the Same Worksheet

Hello,

I hope this post find you all well!

I have a table that has the 3 fields:Product, Value A, Value B, (Value A - Value B ) Difference%,

I also have created 4 parameters : Range A start, Range A End, Range B start, Range B End

Im trying to accomplish the view that is similar to the one in the image attached (please pardon the shadow)

Value A: WIll Aggregate the values of Product in the Range A Start to Range A End

Value B: WIll Aggregate the values of Product in the Range B Start to Range B End

Difference: Calculate the Difference between Aggregated values of A and B

Trend A- Will show the each data point of Value A from Range A start to range A end

Trend B - Will show the each data point of Value B from Range B start to range B end

Is there a way I could accomplish the same using a single worksheet.

I have found this resource that is similar: Tableau Tip: KPIs and Sparklines in the Same Worksheet

Any workarounds and help would be immensely helpful. Thank you.

-Ashwin

• ###### 1. Re: KPIs and Trend Lines on the Same Worksheet

This bears some similarity to another post I helped out with a little while back. Check out https://community.tableau.com/thread/209591 and see if it helps you out too!

• ###### 2. Re: KPIs and Trend Lines on the Same Worksheet

Hi Ashwin,

There are two things that you have to be aware to solve this. The first one is that you can use your measure as as discrete value (look on Blue things and Green things - The Information Lab to better understand the difference between continues and discrete values). The second, is that by making your measure discrete, you will slice your chart by each value that this measures have, so now you will need a LOD calculation (Overview: Level of Detail Expressions ) to aggregate your measure to the level of detail you are using in the table.

So have a look on the attached workbook, I think you will have a better understanding of what I meant.

Also have a look on this post (Tableau Tip: KPIs and Sparklines in the Same Worksheet ). It explain step by step what I did.

Best,

Hil

• ###### 3. Re: KPIs and Trend Lines on the Same Worksheet

Hi Hildebrando,

I am unable to open the workbook attached since I am not using the latest version of Tableau, could you attach a .twb one ? Thanks for your input!

• ###### 4. Re: KPIs and Trend Lines on the Same Worksheet

What version are you using?

• ###### 5. Re: KPIs and Trend Lines on the Same Worksheet

I am using 9.2. Sorry for the inconvenience and thanks in advance!

-Ash

• ###### 6. Re: KPIs and Trend Lines on the Same Worksheet

Here it is in 9.2.

Best,

Hil

• ###### 7. Re: KPIs and Trend Lines on the Same Worksheet

Thanks Hil,

Although this is what I need, I am now quite able to replicate it for my data. Please see the screenshot below:

A couple of pointers:

1. I have removed the 'date' field from Columns and have added the 4 date parameters - Range A Begin, Range A End, Range B Begin, Range B End, that affect V and Value Ref respectively

2. V and Value Ref are calculated fields

3. I am looking for a trend line that plots points from Range A Begin and Range A End for V  and Range B Begin and Range B End for Value Ref.

Is there a way I could achieve this? Im sorry I can't upload the packaged workbook. I hope this helps.

Thanks again!

-Ash

• ###### 8. Re: KPIs and Trend Lines on the Same Worksheet

Hi Ash,

To be able to accomplish that with the technique I showed, you have to have a date field in your data and you parameters (Range A Begin, Range A End, Range B Begin, Range B End) will serve you as filters to your values. In fact, by putting a date field in your data, you will probably create a reference date-value that you might not even need to use filters in your data.

I hope that helps.

Best,

Hil

2 of 2 people found this helpful
• ###### 9. Re: KPIs and Trend Lines on the Same Worksheet

Thanks again for your promptness, As I am beginning to understand your approach, I tried to recreate the view in the workbook (kindly see attached) you provided. However, on sheet 3, I am unable to create the view you have created

This is an issue that I am encountering for my data as well.

As for the data, I do have a 'Date' field included in my data. SO I can re-create this view in my original workbook.

Thanks again and apologies for the inconvenience

-Ash

• ###### 10. Re: KPIs and Trend Lines on the Same Worksheet

Hi Ash,

If you replicate what I created, you will be good to go. I guess the best way to help you to achieve that is by doing it on your workbook. Could you put some fake data on it and upload it?

Best,

Hil

• ###### 11. Re: KPIs and Trend Lines on the Same Worksheet

I have created a xlsx for the dummy data that is very close to my original data. The view that I have created based on your method can be seen in the attached workbook.

The problem is two-fold:

1. The trend line now appears to be a dot/point instead of a line.

2. Each Metric has a multiple rows of 'Value' fields associated with it.

When I tried to re-create the view using your workbook, (see workbook attached in previous message) as well as on my original workbook, I am encountering the aforementioned issue.

Thus, I have a feeling that I am missing out on some piece of logic.

It would be wonderful if you could tell me what I am missing and I could resolve the same.

Looking forward to hearing back. Thanks!

• ###### 12. Re: KPIs and Trend Lines on the Same Worksheet

Hi Ash,

The piece that you are missing is that you must aggregate your Value by Metric. Do this, double click on SUM(Value) (the pill between Metric and Measure Values) and write this {FIXED [Metric]: SUM([Value])}. Now you are good!

What we just did was use an LOD expression to aggregate SUM(Value) at the Metric level. To have a better understanding of LOD Expressions, check this out: Overview: Level of Detail Expressions

Best,

Hil

1 of 1 people found this helpful
• ###### 13. Re: KPIs and Trend Lines on the Same Worksheet

I just want to pipe in again and say what a great discussion thread this has been. Thanks for being so helpful you two! I love following the thought process and learning some new techniques.

• ###### 14. Re: KPIs and Trend Lines on the Same Worksheet

I realized that just after I shot the response. Thank you so much for helping out.

However, while using the LOD fixed calcs, I noticed that the aggregate doesn't work quite correctly,

For example. if the trend line has three points with values 10, 20 and 30, I want the aggregation of the trend line be displayed on the discrete data mark (in this case, it would be 20)

I am using the following calc: { FIXED [Metric]: AVG([Value ])}

But the result is incorrect. Do let me know, if, in your experience you had to deal with averages using fixed calcs and if so what are the workarounds.

Thanks again Hil. Really appreciate it.

1 2 Previous Next