1 2 Previous Next 15 Replies Latest reply on Aug 31, 2018 10:39 AM by Hari Ankem

# How to create a trend mark in my workbook

Hi all,

I will be greatly appreciated if you can help me make the trend mark in my workbook.

Sample workbook is the Example Workbook ed mc.

The workbook I have used is " how to make a ticker mark".

Thanks,

Helen

• ###### 1. Re: How to create a trend mark in my workbook

Start out by creating the following calculated field, which will leverage the LOOKUP function to get the prior score:

Last Score

// Get the previous score.

LOOKUP(SUM([Score]),-1)

Next, calculate the percent difference.

Trend

// Calculate the percent difference.

(SUM([Score])-[Last Score])/[Last Score]

Now drag Trend over to the measure values card.

Finally, right click on the Trend pill and go to "Format". Choose "Custom" format and type in the following: [positive] ▲; [negative] ▼

You should now have something like this:

2 of 2 people found this helpful
• ###### 2. Re: How to create a trend mark in my workbook

Thank you so much. Can I have the work book please? A bit hard to follow.

• ###### 3. Re: How to create a trend mark in my workbook

Yes, sorry, just added it above. It's in 10.4 format so hopefully that works for you.

If this helps to resolve your question, please be sure to mark this as the "Correct Answer" so we can close the thread and others can quickly find answers to similar questions in the future. Thanks!

2 of 2 people found this helpful
• ###### 4. Re: How to create a trend mark in my workbook

I have two questions:

1. How to make sure trend can show two different colors, when going up, the color is green. when going down, the color is red.

2. How to make other numbers stays black? ( i didn't see the black option in tableau color panel)

Thanks,

Helen

2 of 2 people found this helpful
• ###### 5. Re: How to create a trend mark in my workbook

OK, to do that, create a new calculated field:

Arrow Color

IF [Trend]<0 THEN

"Red"

ELSE

"Green"

END

We want to make to color controlled by both Measure Names and the field above, but if we drag Arrow Color to the color card, it will overwrite Measure Names. So, instead, drag Arrow Color to Detail. Then click on the "..." to the left of the pill and change it to color. Your chart will now be colored by the combination of measure name and "Red" or "Green" as defined in Arrow Color.

Now click on the color card and change the colors individually, making the first four black (or a dark grey), then make "Trend, Green" Green and "Trend, Red" Red. The easiest way to choose the black color is to simply double-click on the items under "Select Data Item" then manually choose black.

See attached workbook.

2 of 2 people found this helpful
• ###### 6. Re: How to create a trend mark in my workbook

Thank you so much.

One last ask, is it possible to put the trend mark in the same cell with the numbers?

Or it has to be a separate line item?

• ###### 7. Re: How to create a trend mark in my workbook

Here is an option you can try:

1. Create calculated fields for positive and negative trends and assign the desired symbol to it.

Positive Trend: IF [Trend]>=0 THEN "▲" END

Negative Trend: IF [Trend]<0 THEN "▼" END

Add both of them to the text mark and assign required colors.

And, you can now place these trend marks beside the score or the percent difference.

2. Now, coming to colors which are not available for you in any palette, you can create custom color palettes with the desired colors.

Hope this helps.

1 of 1 people found this helpful
• ###### 8. Re: How to create a trend mark in my workbook

Thank you so much!

How can I put the trend mark and the number in one column?

• ###### 9. Re: How to create a trend mark in my workbook

Just edit the text mark and bring them all on a single line.

3 of 3 people found this helpful
• ###### 10. Re: How to create a trend mark in my workbook

Hari I have a question:

I have created positive trend and negative trend as you suggested.

And I have modified the text portion into:

But in my file, the arrow is showing everywhere..I only want my arrow to show in the percentage column: YoY and QoQ. Could you please help?

As this show, the absolute \$ amount is showing upward/downward arrow which I don't want, and sometimes it shows two arrow, which is very confusing...could you please help me with my code?

Positive Trend:

IF [\$POS YoY]>=0 THEN "▲"

ELSEIF [\$POS QoQ] >=0 THEN "▲"

ELSEIF   [\$ MGT DA YoY] >=0 THEN "▲"

ELSEIF  [\$MGT DA QoQ] >=0 THEN "▲"

END

Negative Trend:

IF [\$POS YoY]<0 THEN "▼"

ELSEIF [\$POS QoQ] <0 THEN "▼"

ELSEIF   [\$ MGT DA YoY] <0 THEN "▼"

ELSEIF  [\$MGT DA QoQ] <0 THEN "▼"

END

Is there anything I can do to specify in my code that, I only want arrow in four measure values, which is \$POS YoY, \$POS QoQ, \$MGT DA YoY, \$MGT DA QoQ?

Thanks and best regards,

Helen

• ###### 11. Re: How to create a trend mark in my workbook

I am trying to mimic the sample data.

But here my POS data is a time series data by quarter (continuous) and my \$POS YOY is table calculation and it is a bit hard to create.

It is my company database so I can't share.

Thanks,

Helen

• ###### 12. Re: How to create a trend mark in my workbook

Any calculations for trends should be based on a single measure only. You are trying to do it using multiple measure values. If you need to calculate the trend for each of the measures, then please create separate calculated fields.

• ###### 13. Re: How to create a trend mark in my workbook

I have created calculation for each trend, but when I apply one, the trendmark is still everywhere....

Please help me to identify how I can only show t rend mark around the trend percentage.

Thanks,

• ###### 14. Re: How to create a trend mark in my workbook

Dear Ken,

Thank you so much for this wonderful answer. It is very intelligent that you have used " "Format". Choose "Custom" format and type in the following: [positive] ▲; [negative] ▼" to show the trend mark.

I am just wondering, if there is anyway that I can edit the color of the trendmark in  the format as well?

For me , I don't create a separate calculation for trendmark. When i do that, the trendmark will apply all across.

Thanks,

Helen

1 2 Previous Next