1 2 Previous Next 18 Replies Latest reply on Nov 13, 2016 8:35 AM by Khalid Baker

# How Can We Use Up and Down Arrows for Specific Columns?

Suppose Company is comparing current month sales vs a year ago (same month) sales vs planned sales for the current month. You have 2 columns depicting the % change and want arrows showing increase or decrease in those 2 columns.

I used if then else statement with elseif statements, it implements correctly to a single column and not both.

This is the syntax I used:

IF [Current vs planned %] >0 THEN "Up"

ELSEIF  [Current vs planned] < 0 THEN "Down"

ELSEIF  [Current vs Year ago %] > 0 THEN "Up"

ELSEIF  [Current vs Year ago %]< 0 THEN "Down"

END

May anyone help me?

• ###### 1. Re: How Can We Use Up and Down Arrows for Specific Columns?

Hi , in the calculation which you have created it will end up in result of any first 2 conditions always , it will not go for 3,4 statements i.e Current vs Year ago %. So it will show correct result for only Current vs planned % only , create 2 different calculations and different buttons for 2 columns (Current vs planned %,Current vs Year ago %).

1 of 2 people found this helpful
• ###### 2. Re: How Can We Use Up and Down Arrows for Specific Columns?

Hey Khalid,

Can you please attach sample workbook with the dummy data??

--Ashish

• ###### 3. Re: How Can We Use Up and Down Arrows for Specific Columns?

Hi Hima, The issue is with 2 calcs is that one calc can be a shape and not both. When you drag it as a mark/ shape than only one calc can exist there.

I tried that. May we do a nested if function?

• ###### 4. Re: How Can We Use Up and Down Arrows for Specific Columns?

Done!

• ###### 5. Re: How Can We Use Up and Down Arrows for Specific Columns?

Yes , Use Nested IF like if both are >0 then Up , if both are <0 then Down , But decide what shape to give when one is >0 and the other is <0 . May be based on Priority of filed you can decide to choose the shape when one is >0 and the other is <0.

IF [Current vs planned %] >0  and [Current vs Year ago %] > 0 THEN "Up"

ELSEIF  [Current vs planned] < 0 and [Current vs Year ago %]< 0 THEN "Down"

END

• ###### 6. Re: How Can We Use Up and Down Arrows for Specific Columns?

It will only execute the first 2

IF [Current vs planned %] >0  and [Current vs Year ago %] > 0 THEN "Up"(Execute up to here)

ELSEIF  [Current vs planned] < 0 and [Current vs Year ago %]< 0 THEN "Down"

END

The other issue is that it also add shapes to every column when i only want it on the following columns:

Current vs Year ago %

Current vs planned %

• ###### 7. Re: How Can We Use Up and Down Arrows for Specific Columns?

hi Khalid,

So another way you could do this, is to use custom formatting.

In the attached I created  a new field

[%age Change vs Plan +/-]

[% change vs Plan]-100

so this just makes thew variance + or -

From this field, I can then use the following custom formatting

â–² +0;â–¼ -0;â–º 0

by hard coding an Ascii character in the formatting, + shows as up arrow, and - as down arrow. There are lots of Ascii arrows that we can use directly as strings like this.

Hope this helps.

3 of 3 people found this helpful
• ###### 8. Re: How Can We Use Up and Down Arrows for Specific Columns?

That is great!

But I would like to have the arrow to next to [% change vs Plan] & [Current vs Year ago %]. so [% change vs Plan] >100 good up arrow

[% change vs Plan] <100 bad down arrow. and same for [Current vs Year ago %]

• ###### 9. Re: How Can We Use Up and Down Arrows for Specific Columns?

Yes we can do that!...we just need to repeat the same process for the vs Last Year measure. With this method, we can set the formatting for each measure induvidually....version attached with both measures.

• ###### 10. Re: How Can We Use Up and Down Arrows for Specific Columns?

Simon I want the arrow on the same column [% change vs Plan] & [Current vs Year ago %]. I wouldn't want to add columns if possible, and would like arrows with colour coding if possible. Meaning if it is above 100 green and below is red.

Thanks

• ###### 11. Re: How Can We Use Up and Down Arrows for Specific Columns?

hi Khalid,

Just to check my understanding, you only want 1 arrow? for both measures?...if so what arrow direction if one is up, and one is down?

To get colouring, we'll probably have to use a slightly different technique (as we can only have one colour for the measures)...but if you only want one arrow (so the answer to question above) we may be able to do it this way.

In the "Devs on Stage" at TC16 last night they did announce a new feature to allow separate colours by measure (but no time scale to when it would be released!)

• ###### 12. Re: How Can We Use Up and Down Arrows for Specific Columns?

not exactly. if you open the original workbook my attempt was to add arrows on [% change vs Plan] & on the [Current vs Year ago %]. the issue was the execution of the formula as well as arrows appeared everywhere. I would like arrows for each value under the mentioned columns. If it is above 100 than good and if it is below 100 not good. So i want the formula to work for both columns since one calc can only be a shape.

IF [Current vs planned %] > 100 THEN "Up"

ELSEIF  [Current vs planned] <  100 THEN "Down"

ELSEIF  [Current vs Year ago %] > 100 THEN "Up"

ELSEIF  [Current vs Year ago %]<  100 THEN "Down"

END

So only the top 2 executed.

• ###### 13. Re: How Can We Use Up and Down Arrows for Specific Columns?

I think I see!

So if you want 2 different shapes and colours (relating to the up/down of the 2 measures), then we won't be able to use the Custom Format trick (as we can't apply different colours to different measures, when using measure names/value...until the new feature is released).

So one way round this is to use MIN(1) as a dummy axis for setting out, we can then create as many of these as we like (2 in this case), and then we have full control over each column/measure...in both colour and shape.

In the attached, I've created 2 separate fields to assess the up/down of each measure and used MIN(1) to show each of them. One issue that arises here, that as Axis, we don't get a top header (as the title goes on the axis), to get round this I've dual-axis'ed the chart, and removed the Marks and Axis title from the lower one, and renamed (and removed marks) from the top one...quite a bit of work, which is why you can see why Tableau have developed the new feature!!

1 of 1 people found this helpful
• ###### 14. Re: How Can We Use Up and Down Arrows for Specific Columns?

Exactly! Now I will have to open your workbook to figure out what you did exactly. Like the steps you took. Since this option is very good when it come to visualizing in a table format! Thanks Mr. Simon!

1 2 Previous Next