8 Replies Latest reply on Jan 20, 2018 4:14 PM by Virginie Bocard

Adding Up/Down/Same Arrow on Dashboard

Hello!

I have data from a survey comparing two period of time.  I wrote this formula which is valid but it does not return the correct answer.  For Q1 Overall, how satisfied (for example), the result should be Up.

I'm attaching the workbook in Tableau v10.1

IF ZN(SUM([Nr. of Responses])) - LOOKUP(ZN(SUM([Nr. of Responses])),-1) < 0 THEN "Down"

ELSEIF ZN(SUM([Nr. of Responses])) - LOOKUP(ZN(SUM([Nr. of Responses])),-1) = 0 THEN "Same"

ELSEIF ZN(SUM([Nr. of Responses])) - LOOKUP(ZN(SUM([Nr. of Responses])),-1) > 0 THEN "Up"

ELSE "n/a"

END

Thank you.

• 1. Re: Adding Up/Down/Same Arrow on Dashboard

Hi Virginie,

So what is happening with your calculation is the difference from previous is returning a negative value so all will be labelled "down" see below:

To show the right value simply wrap all the function around brackets and stick in a minus:

Try to always see the numbers of the different steps in your calculation

Hope this helped!

Sasha

• 2. Re: Adding Up/Down/Same Arrow on Dashboard

Thanks, Sasha!  But is there a way to display the information on a readable format?

It is not easy to read, nor to graph.  Any suggestions?

Thanks!

Virginie

• 3. Re: Adding Up/Down/Same Arrow on Dashboard

I had a different take on this:

- I'm guessing that the up/down/same is supposed to be on the % of total, not the number of respondents (because the # of respondents is lower for every single value). In that case the Up/Down/Same calculation needs to change.

- The % of total calculation requires using Hide to hide the dissatisfied results. I've found this to be problematic for maintenance of visualizations because there's no obvious indicator that data has been hidden and that it makes it harder for people who are looking at the viz later to figure out what is going on. There are two common workarounds: one is to use a table calculation filter, the other is to use a record-level evaluation embedded in an aggregation. I used the latter in this case because it makes other parts easier.

- The existing Nr. of Responses measure uses a FIXED level of detail expression with COUNTD(). COUNTD() is slower than most other aggregations and the FIXED LOD is going to force an aggregation to be made. Given the following three factors that I could see in the data and the view: a) a 1:1 relationship between Email & Respondent ID; b) that there is only 1 record per Respondent ID and question/quarter and c) the viz Level of Detail includes question & quarter as dimensions then we can then get the % of total results as a regular aggregate that is basically a modified Excel SUMIF(). Here are the three calculations:

# of Responses jtd is IF [Answer (group)] != 'N/A' THEN 1 ELSE 0 END

# of Satisfied jtd is IF [Answer (group)] = 'Satisfied & Very Satisfied' THEN 1 ELSE 0 END

% of Total Sat is now SUM([# of Satisfied jtd]) / SUM([# of Responses jtd])

This no longer requires putting Answer (group) onto Filters to remove the N/A's nor putting Answer (group) into the viz and hiding the dissatisfied group, here's a workout view:

Then the up/down/same Latest % of Total calculation has the following formula with a compute using on the FY-Qtr:

IF LOOKUP([Diff from Prior % of Total Sat],LAST()) < 0 THEN

'⬇︎'

ELSEIF LOOKUP([Diff from Prior % of Total Sat],LAST()) = 0 THEN

'•'

ELSEIF LOOKUP([Diff from Prior % of Total Sat],LAST()) > 0 THEN

'⬆︎'

ELSE

''

END

The LOOKUP([Diff from Prior % of Total Sat],LAST()) returns the latest value of the results and we can use it to generate this view:

Or if we don't need to show the exact value then we can do an even more compact layout like this where I used a simplified up/down/same calculation:

v10.1 workbook is attached.

Jonathan

1 of 1 people found this helpful
• 4. Re: Adding Up/Down/Same Arrow on Dashboard

This is great Jonathan.  Thanks so much.

One quick question, how do you get the arrow picture.  When I opened the workbook, it gives me a square instead.

One more thing … how would you go about graphing this as bar charts? (instead of a table)?

Cheers,

Virginie

Virginie D. Bocard

ITSCS – Program, Initiatives & Analytics Team

Respectful Workplace Advisor for ITS

T +1 (202) 473-9665

vbocard

1818 H Street, NW; Washington, DC, 20433

• 5. Re: Adding Up/Down/Same Arrow on Dashboard

Hi Virginie,

I'm guessing that the squares you are getting are because I created mine on the Mac and you're on Windows. You can use the Character Map app on Windows to insert arrows and other special characters.

As for making a bar chart can you provide a mockup of what you are looking for?

Jonathan

• 6. Re: Adding Up/Down/Same Arrow on Dashboard

Oh got it for the arrow code. That makes sense.

As for the graphs, this is what I’ve been doing manually in PowerPoint and Excel (see screenshot attached).

Thanks for all the help. This is greatly appreciated.

Cheers,

Virginie

• 7. Re: Adding Up/Down/Same Arrow on Dashboard

Can you explain how the numbers for the arrows come about? I don’t see how they relate to the bars.

Sent from my iPhone

• 8. Re: Adding Up/Down/Same Arrow on Dashboard

Hi Jonathan!

The arrows come from the difference from previous year. So basically it is your calculation of the difference between FY18Q3 vs FY18Q2.

I hope it clarifies.

Virginie