1 2 Previous Next 26 Replies Latest reply on Nov 12, 2013 5:52 AM by Murali Govindu

    Variance with Colors - Possibilities?

    Murali Govindu

      Updated with Packaged workbook for your ready reference and help (u may see various cal. but none are close enough, however, I am sure, little more modifications to these cal., may be good enough for a decent solution. The denormalized sheet may not work - as I was looking for solution in denormalized tables also, i just included this sheet) - Thanks

       

      Thanks Matt, I tried before posting here and couldn't do much as I was working on an extract!

       

      However, I will take this opportunity to narrate this workbook clarity:

       

      1st sheet - VARIANCE FLAG COLORS

       

      Columns:

      Focus_product_attribute_description (from table: EMDM_Focus_Product_Attributes)

      Focus_product_attribute_details (from table: EMDM_Focus_Product_Attributes_details)

       

      Rows:

      COT_Description (from table: EMDM_YTD_Results_Dsb)

       

      Values on the Dashboard:

      Results_Fact (from table: EMDM_YTD_Results_Dsb)

       

      Filters:

      Hidden - Var_Flag (from table: EMDM_Focus_Product_Attributes_details)

       

      (Var_Flag column has values 1, 2 and 4 - Each record will be flagged in database based on (1) >0% Var. 2) -5% to 0% Var. 3) < -5% Var.) with one of this values i.e., either 1 or 2 or 4)

       

      Parameter:

      Var_Flag

       

      Calc.Field:

      VARIANCE FLAG CAL. (purpose is to bring-in Var_Flag column with a color code i.e., Red, Green and Yellow which needs to be displayed at a row level on the dashboard)

       

      2nd sheet - Variance Normalized


      In this sheet Var_Flag column is not available so not hidden.  The purpose is to eliminate the calculations in database based on 1) >0% Var. 2) -5% to 0% Var. 3) < -5% Var. and flag them for reporting purposes.

       

      I want to determine based on the column 'Growth %' which type of category each record belongs to:

      1) >0% Var. 2) -5% to 0% Var. 3) < -5% Var.

       

      3rd sheet - Variance in Denormalized

       

      If we cannot come up with normalized database from 1st sheet kind, I want to see if the same can be achieved from a Denormalized table wherein all the values will be in one Table and no need of any joins so that the columns are available right in the measures unlike measures columns like 1st sheet, and we can be able to determine the 'growth %' which takes Previous Year and Current Year sales difference in % and categorize them into 1) >0% Var. 2) -5% to 0% Var. 3) < -5% Var. with color coding Red, Green and Yellow respectively.

       

      I hope you will appreciate the above and bring some thoughts.  You may see some of my calculations which are not working right for an idea.  Thanks and appreciate your quick inputs.

       

      Hi,

       

      Is there any possibility to show a variance column on a dashboard in different colors which should go with the specified range of change if variance is about x % of change then RED, if it is y% of change then BLUE, etc.?

       

       

       

      I am trying to figure out the ways and means to achieve a variance between two columns that derive from a fact table.

       

      I have:

       

      - Region table with Region names

      - Attributes table with sales for this year and previous along with Variance columns (pre-calculated)

       

      Now, I am trying to see if I can color code the variance based on conditions like >0%, -5% to 0% and <-5%

       

      I would be easy for me had I cal. the sales columns (previous and current) and derive the % of change, but this is coming from separate tables and need to achieve a color for specific condition as detailed above.

       

                               GV Sireesha's Region (comes from Region Table)

      Current          Previous       Variance     Variance % (all these columns comes from Attributes table)

      786                   1436          -650            -45%        (all these columns comes from Attribute Fact table)


      Thank and regards,

      murali.govindu@gmail.com

       

       


        • 1. Re: Variance with Colors - Possibilities?
          kevin.lynch

          Hi Murali,

           

          Have you had a look through Jonathon Drummey's Conditional Formatting workbook?

           

          http://public.tableausoftware.com/views/conditionalformattingv4/Introduction#1

           

          He covers a lot of different options for achieving formatting options similar to what you are asking for, I think.

           

          Best to download it rather than viewing on Tableau Public, it reads better on desktop.

           

          Kevin

          1 of 1 people found this helpful
          • 2. Re: Variance with Colors - Possibilities?
            Matt Lutton

            Murali:

             

            If you can post a packaged workbook (.twbx), we will be happy to take a look and see if we can find a proper solution for you.  Very difficult to help without one to refer to.  You could mock up a scenario using sample data if your data is sensitive.

             

            Cheers.

            • 3. Re: Variance with Colors - Possibilities?
              Murali Govindu

              Thanks, I know something I this I have seen earlier but didn't bookmark it.

               

              I will look at this now and see if I get an idea to resolve.

              • 4. Re: Variance with Colors - Possibilities?
                Murali Govindu

                Hi Matt,

                 

                I am almost finished building a sample workbook for the asked help but afraid I may be hit by the Tableau scheduled community sites shutdown for maintenance.

                 

                Guys, I don't have access to this site through my email messages, I have only these log in credentials and works best (in case) to communicate with my email id murali.govindu@gmail.com

                • 5. Re: Variance with Colors - Possibilities?
                  Murali Govindu

                  Hi Matt, It took sometime, but can you look at my workbook and suggest me pl.? Thanks

                  • 6. Re: Variance with Colors - Possibilities?
                    Murali Govindu

                    Hi Kevin,

                     

                    Your suggestion has made me to enrich my knowledge, however, I attached a workbook now looking forward for a possible solution!  I am sure I am very much close to the solution but missing some think here .... look for help.

                    • 7. Re: Variance with Colors - Possibilities?
                      Matt Lutton

                      What are you expecting as an end result?  I would suggest when you post a workbook on the forum that you only include the fields and sheets necessary--I am immediately confused when I open this workbook because of the # of fields, etc.  Please trim down, and let us know what you are expecting as a result.

                      • 8. Re: Variance with Colors - Possibilities?
                        Murali Govindu

                        Thanks Matt, I tried before posting here and couldn't do much as I was working on an extract!

                         

                        However, I will take this opportunity to narrate this workbook clarity:

                         

                        1st sheet - VARIANCE FLAG COLORS

                         

                        Columns:

                        Focus_product_attribute_description (from table: EMDM_Focus_Product_Attributes)

                        Focus_product_attribute_details (from table: EMDM_Focus_Product_Attributes_details)

                         

                        Rows:

                        COT_Description (from table: EMDM_YTD_Results_Dsb)

                         

                        Values on the Dashboard:

                        Results_Fact (from table: EMDM_YTD_Results_Dsb)

                         

                        Filters:

                        Hidden - Var_Flag (from table: EMDM_Focus_Product_Attributes_details)

                         

                        (Var_Flag column has values 1, 2 and 4 - Each record will be flagged in database based on (1) >0% Var. 2) -5% to 0% Var. 3) < -5% Var.) with one of this values i.e., either 1 or 2 or 4)

                         

                        Parameter:

                        Var_Flag

                         

                        Calc.Field:

                        VARIANCE FLAG CAL. (purpose is to bring-in Var_Flag column with a color code i.e., Red, Green and Yellow which needs to be displayed at a row level on the dashboard)

                         

                        2nd sheet - Variance Normalized


                        In this sheet Var_Flag column is not available so not hidden.  The purpose is to eliminate the calculations in database based on 1) >0% Var. 2) -5% to 0% Var. 3) < -5% Var. and flag them for reporting purposes.

                         

                        I want to determine based on the column 'Growth %' which type of category each record belongs to:

                        1) >0% Var. 2) -5% to 0% Var. 3) < -5% Var.

                         

                        3rd sheet - Variance in Denormalized

                         

                        If we cannot come up with normalized database from 1st sheet kind, I want to see if the same can be achieved from a Denormalized table wherein all the values will be in one Table and no need of any joins so that the columns are available right in the measures unlike measures columns like 1st sheet, and we can be able to determine the 'growth %' which takes Previous Year and Current Year sales difference in % and categorize them into 1) >0% Var. 2) -5% to 0% Var. 3) < -5% Var. with color coding Red, Green and Yellow respectively.

                         

                        I hope you will appreciate the above and bring some thoughts.  You may see some of my calculations which are not working right for an idea.  Thanks and appreciate your quick inputs.

                        • 9. Re: Variance with Colors - Possibilities?
                          Matt Lutton

                          Hi Murali:

                           

                          You can SAVE AS and create a copy of your workbook and remove all the unnecessary stuff.  Remove any fields that are not pertinent to the view or the question you have. Remove additional sheets that we should not focus on.  It would be ideal to post a workbook with only one or two sheets, and tell us what you would like the resulting view(s) to look like.

                           

                          You started with a very simple question, so I'd suggest you go back to that--post a workbook set up the way you want it, and tell us what you would like to color.  We can then tell you what is possible.

                          • 10. Re: Re: Variance with Colors - Possibilities?
                            Murali Govindu

                            Hi Matt,

                             

                            How about this workbook please?

                             

                            Thanks for the suggestion.  Let me know if you need any clarification.  I am not deleting my earlier sample workbook as sometimes that may give an idea to resolve.

                             

                            Regards,

                            murali.govindu@gmail.com

                            • 11. Re: Re: Variance with Colors - Possibilities?
                              Matt Lutton


                              I'm sorry to keep disappointing, but I am at a loss--the data appearing in your table all comes from a single field Results_Fact.  Therefore, I have no idea how to color any values in this data.  Usually, we could just do something like:

                               

                              IF sum(Variance)>0 then "green"

                              elseif ... blah blah blah

                               

                              However, that requires your data to include a field for each measure column.  I am unsure how to approach your scenario with the current state of your data.  There may be a way, but it looks like I'm at a loss with this dataset

                              • 12. Re: Re: Variance with Colors - Possibilities?
                                Jonathan Drummey

                                See the attached for two options. For scenario 1 (Var_Flag is precalculated), I used four calculated fields - one to return the value of Var_Flag, a table calculation to propagate the value of Var_Flag to every row, then a color calc, and finally a table calc-based filter to hide the Var_Flag field.

                                 

                                For scenario 2, I created measures based on the data (essentially unpivoting the data) and used that for coloring, however the data seems very sparse because only one COT_Key/FPA_Description combination has a % Growth field.

                                 

                                The advantage to scenario 1 is that since you've already fully pivoted the data, the layout is really easy. You can even get the formatting right by creating measures like for scenario 2. If you only want to color a single column, you can adjust the color calculated field to ignore those dimensional values that shouldn't be colored.

                                 

                                Scenario 2 will require more effort to get the table calculations right and is more limited in terms of formatting because Tableau only allows us a single instance of Measure Names/Values in a view.

                                 

                                Jonathan

                                • 13. Re: Variance with Colors - Possibilities?
                                  Murali Govindu

                                  Thanks for your insight Matt.

                                   

                                  I have a flexibility to add the columns in the database still, that is the reason I was seeing if the Var_Flag can help me out. 

                                   

                                  However, as explained in my one previous (detailed) message along with referring to my earlier workbook posted here, I am very close to achieve coloring per Var_Flag by creating a Parameter with values 1 = Green, 2 = Yellow and 3 = Red and this Parameter I used on Calc. to assign colors (since these are not direct Measures). 

                                   

                                  I also, tried to reference the Key values of Attributes_details and Attributes_key to see if I can hold the Var_Flag and maneuver it (this is also listed in my previous workbook posted here).

                                   

                                  In the light of the above, will you mind giving a last try and put-forth your suggestions?

                                   

                                  Thanks a lot, best regards,

                                  • 14. Re: Re: Variance with Colors - Possibilities?
                                    Matt Lutton

                                    Murali:

                                     

                                    Believe me, you are in very capable hands now.

                                     

                                    Jonathan--I really wish I could wrap my head around finding solutions like scenario 1.  That's amazing you were able to do that so quickly.  I know you've had a lot of practice, but I just really wish this stuff came easier to me!

                                    1 2 Previous Next