10 Replies Latest reply on Nov 6, 2018 11:49 AM by Mei Ming

    how to compare aggregate value with corresponding cutoff

    Mei Ming

      Hi All,

       

      I have some technique issue for Tableau and hope to get your help. Thanks in advance!

       

      I have one dimension with multiple categories. I calculated the sum of (number of records) for each categories for each department. I have the minimum requirement value for each category. Now I want to compare the sum value for each category/department with the minimum to see whether they above or below the minimum. For each department, I need to mark the categories below minimum in red (like heatmap), and also I want to calculate the percentage of those categories below minimum for all departments, etc.

       

      Thank you and any suggestions would be greatly appreciated!

      Mei

        • 1. Re: how to compare aggregate value with corresponding cutoff
          Jennifer VonHagel

          Hi Mei Ming,

           

          This is certainly do-able! Can you attach a .twbx workbook with sample data?  The answer will depend on how your data is set up, particularly the relationship between actual values and category minimums.

           

          Best,

          Jennifer

          • 2. Re: how to compare aggregate value with corresponding cutoff
            Mei Ming

            Hi Jennifer,

             

            Thank you for your response. I am not sure what is the best way to display the relationship between aggregated values with minimum criteria. Right now I have the original raw data which I used for aggregation for department/category and another excel sheet for category minimum. I joined those two sheets by category name, so the view output would have the minimum as the dimension and aggregated values as measure. I will figure out how to upload the sample data if that is helpful.

             

            Do you have any better suggestion for better displaying the data so that I can get what I want.

             

            Thanks a lot,

            Mei

            • 3. Re: how to compare aggregate value with corresponding cutoff
              Jennifer VonHagel

              Hi Mei,

               

              Yes, if you could attach a .twbx with the data as you have it that would be great. It will help me (and the community) make more specific suggestions or recommendations both for your original request and if there might be a better way to connect the data. .

               

              Best,

              Jennifer

              • 4. Re: how to compare aggregate value with corresponding cutoff
                Mei Ming

                Hi Jennifer,

                 

                I attached sample.twbx file for your reference. You will see I have two different data sets - one is the raw (one row is one transaction) and another is the minimum requirement data. I joined them with department and category, then get an aggregated value for each department/category (see sheet1) with minimum requirement for each dep/category. Basically I would like to get a heatmap to mark those sum(number of record) below minimum for each dep/category, and also compare the department performance with some metrics (such as unique saleId percentage below minimum - (number of saleId with at least one category below minimum/total count of saleId for each dept), category percent below cutoff - total categories (unique count and non-unique count) below minimum for all saleID/total categories (count of saleId *  count of category for each dept).

                 

                Let me know if you need any additional info and thanks again for your help.

                 

                Thanks,

                Mei

                • 5. Re: how to compare aggregate value with corresponding cutoff
                  Jennifer VonHagel

                  Great. One last question:

                   

                  Are you comparing the minimum to the count in each sales id (3) or to the category as a whole (6)?

                   

                   

                  Best,

                  Jennifer

                  • 6. Re: how to compare aggregate value with corresponding cutoff
                    Mei Ming

                    Yes, Jennifer. I want to compare the minimum with count for each saleId as you marked in above snapshot. Typically I also want to see percentage of saleId (unique count saleId) with at least one category below cutoff for each dept.

                     

                    Thank you so much for your help!

                     

                    Thanks,

                    Mei

                    • 7. Re: how to compare aggregate value with corresponding cutoff
                      Jennifer VonHagel

                      Hi Mei,

                       

                      I've attached the workbook with some calculations and sample charts that should get you started.  Have a look and let me know if you have questions. If you haven't worked with LOD (Level of Detail) calculations before (FIXED, INCLUDE, EXCLUDE), see here: Level of Detail Expressions - Tableau . In the calculations below, I've only used the FIXED type of LOD.

                       

                      Here are a few calculations I created as a basis:

                       

                      Item Cnt: This is the same as [Number of Records], but more descriptive for calculations.

                      1

                       

                      Trx Row: This simply creates an explicit dimension which is the level at which to compare Number of Items to your Minimum (or Target)

                      [Sale Id] + '_' + [Department] + '_' + [Category]

                       

                      Trx Row Variance: Finds the difference between the Item Cnt and Minimum for each Trx Row.  I've wrapped it in "ATTR" because we don't want to SUM() across rows, we simply want the number at the Trx Row level of detail.

                      ATTR(

                        { FIXED [Trx Row] : SUM([Item Cnt]) } - [Minimum]

                      )

                       

                      Trx Row Variance %: Finds the variance as a % from the Minimum or Target.

                      ATTR(

                        ({ FIXED [Trx Row] : SUM([Item Cnt]) } / [Minimum]) - 1

                      )

                       

                      Trx Row Meets Target: Categorizes each TrxRow as True or False based on whether its Item Cnt is below the minimum or not.

                      { FIXED [Trx Row] : SUM([Item Cnt]) } >= [Minimum]

                       

                      Target: This is a copy of [Minimum], but classified as a measure rather than a dimension. For some types of charts it's better for this to be a measure.

                       

                      With these calculations, you can do standard Actual vs Target, Variance type charts:

                      Not sure what you had in mind for a heatmap, but here's one option. Bar charts are easier for me to visually process.

                       

                      And here are some calculations that will help you find number of SalesIDs per Department that have at least one TrxRow not meeting the Minimum:

                      SalesID Uq Cnt:

                      COUNTD([Sale Id])

                       

                      SaleIDs has TrxRow < Minimum: True|False field for each Sale ID whether that Sale ID has at least one Trx Row not meeting the minimum

                      { FIXED [Sale Id] : MAX(IF NOT [Trx Row Meets Target] THEN 1 END) }

                       

                      % SaleID TrxRow < Min: % Sales IDs which have at least one Trx Row not meeting the minimum. In your sample data, this is all of them.

                      COUNT({ FIXED [Sale Id] : MAX(IF NOT [Trx Row Meets Target] THEN 1 END) }) /

                      COUNTD([Sale Id])

                      • 8. Re: how to compare aggregate value with corresponding cutoff
                        Mei Ming

                        Hi Jennifer,

                         

                        This looks amazing. However, I won't be able to open the attached workbook you uploaded. I guess it may be caused by the different version of tableau. I am currently using 10.3. Is it possible for you to reattach it?

                         

                        Thanks,

                        Mei

                        • 9. Re: how to compare aggregate value with corresponding cutoff
                          Jennifer VonHagel

                          Oh right, see if this works, I had to switch out datasources (from .hyper to .tdsx) - hopefully everything translates ok.

                           

                          Best,

                          Jennifer

                          • 10. Re: how to compare aggregate value with corresponding cutoff
                            Mei Ming

                            Thanks, Jennifer. It works pretty well. I will implement a little bit and change the mark here as 'answered'.

                             

                            I am deeply grateful for your help!

                             

                            Thanks,

                            Mei