5 Replies Latest reply on Mar 31, 2017 9:25 AM by Okechukwu Ossai

    Color the First Column of a highlight table

    joe sparty

      Hello,

       

      I am recalculating a system value and have created a computed field to label it; positive, zero, negative.  Once the user clicks on one of the labels in the summary I would like to to show: Invoice, Diff_Total_Recalc, Total, Total Recal.  The Diff_Total_Recalc column will have the difference between the 2 values.  I would like that column colored as a highlighted table to show the variances, is that possible in v10.2?

       

      Thanks for the help,

      -Joe

        • 1. Re: Color the First Column of a highlight table
          Okechukwu Ossai

          Hi Joe,

           

          I'm not sure if v10.2 can achieve exactly what you want. However, you can get it done with a bit of effort.

           

          Create 3 dual axis columns - 1 Bar and 1 Text mark for each axis. These 3 columns are placeholders for your 3 measure fields.

          Create a calculated field [Blank] to cover the "abc" text.

          Place the measures in the right columns and format the columns to remove default color. Fix the axis for teh bars from 0 to 1.

          Remove tick marks and titles where necessary. See attached workbook to see how I built the table.

           

          I also modified your [Diff_Color] calculation

          IF [Diff_Total]<0 THEN 1

          ELSEIF  [Diff_Total]>0 THEN 2

          ELSE 3

          END

           

          Let me know if you found this helpful.

           

          Ossai

          1 of 1 people found this helpful
          • 2. Re: Color the First Column of a highlight table
            joe sparty

            Thanks Ossai, this is what I was looking for.  Your workbook was helpful but I'm still off, how did you create the 3 dual axis columns?  Did you do them 1 at a time? 

             

            First I matched your workbook by columns and rows.  Then for each Axis 2 I did a right click Dual Axis.  Then for each Axis 1 I did a right click Sync Axis. 

             

            so_close.png

             

            When & how did yo fix the axis for the bars from 0 to 1?

             

            Also the 'Stacked Bars' under Show Me is greyed out, how did you switch yours to stacked bars?

            • 3. Re: Color the First Column of a highlight table
              Okechukwu Ossai

              You are almost there.  You can either create the 3 dual axes together or one at a time. See the steps below.

               

              Drag [Invoice Nbr] to the Row shelf. Drag the [Axis 1] and [Axis 2] to the Column shelf as shown below.

               

               

              Then create 3 dual axis columns. Choose bar marks for SUM(Axis 1), SUM(Axis 1) (2) and SUM(Axis 1) (3).

              Choose Text marks for SUM(Axis 2), SUM(Axis 2) (2) and SUM(Axis 2) (3).

              Then remove Measure Names from the color marks card of all the marks.

               

               

              Your view should look like this one below

               

              Right click on Axis 1 and select Edit Axis.

               

               

              On the General tab, fix the axis between 0 and 1 and delete axis title. On the Tick Marks tab, remove all tick marks.

              Do this for all the 3 lower Axes 1.

               

               

              Your view will look the one below.

               

              Reduce the size of the columns by dragging from the side of the third column. on the first SUM(Axis 1) marks, left click on the color marks card and select white.

               

               

              Do this for SUM(Axis 1) (2) and SUM(Axis 1) (3). Your view should look like this.

               

               

              Now drag your measures to the Marks. Drag [Diff_Total] to the Text marks card of SUM(Axis 1).

              Drag [Total] to the Text marks card of SUM(Axis 1) (2). And Drag [Total Recal] to the Text marks card of SUM(Axis 1) (3).

               

               

              Then drag the [Blank] calculated field to the Text marks card of SUM(Axis 2), SUM(Axis 2) (2) and SUM(Axis 2) (3).

               

               

              Right click in one of the columns and select Format. Format the columns as shown below

               

               

               

               

              Your view should look like this by this stage.

               

              Then right click on Axis 2 and Edit axis as below. Notice the white space before the Diff_Total. This is used to left align the title.

              Repeat for the other 2 columns.

              Then right click on each Title, select Format to format font as required.

               

               

              Your view should look like this.

               

               

              Then drag the top row line up. Right click on "Invoice Nbr" header and format alignment

               

              Now your table should look like this. We are almost there!

               

              Drag [Diff_Color] to the color marks card of SUM(Axis 1) and assign colors as required. And that's it!!

               

              Hope this helps.

               

              Ossai

              1 of 1 people found this helpful
              • 4. Re: Color the First Column of a highlight table
                joe sparty

                Thanks for the screen shots, I've been using them and it has been very helpful.