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

    Adding Up/Down/Same Arrow on Dashboard

    Virginie Bocard

      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

       

      Can you advise?

      Thank you.

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

          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
            Virginie Bocard

            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
              Jonathan Drummey

              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:

               

              Screen Shot 2018-01-19 at 9.43.24 AM.png

               

              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:

               

              Screen Shot 2018-01-19 at 9.47.30 AM.png

               

              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:

              Screen Shot 2018-01-19 at 9.48.54 AM.png

               

              v10.1 workbook is attached.

               

              Jonathan

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

                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

                ITIL© Certified

                Respectful Workplace Advisor for ITS

                T +1 (202) 473-9665

                E vbocard@worldbankgroup.org<mailto:vbocard@worldbankgroup.org>

                vbocard

                1818 H Street, NW; Washington, DC, 20433

                Questions on RWA’s role? Click for more information<http://spcapps.worldbank.org/sites/IJS/SitePages/RespectfulWorkAdvisor.aspx>

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

                  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
                    Virginie Bocard

                    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
                      Jonathan Drummey

                      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
                        Virginie Bocard

                        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