12 Replies Latest reply on Aug 15, 2016 8:49 AM by Suvojit Basu

    Question on Distinct Count based on a specific condition

    Suvojit Basu

      Hi All,

       

      I'm a beginner on Tableau and have a question on distinct count based on a specific condition. I'm working on a data file which essentially has pricing data on products. The data has fields like Product ID, Product description, Store ID, Audit Data, Price. I want to create a table which has products in rows and 3 columns where I want to calculate number of stores where price is less than, equal to, and more than a specified price. The "specified price" could be drawn from a separate table which has my 'ideal price point' (joined with my data table on product ID) OR it could be the average price of the product across all stores. The table could look like this. I also intend to put date as filter for the user to have the ability to look at different periods.

       

      Product
      # Stores where Price < 'Specified Price'# Stores where Price = 'Specified Price'# Stores where Price > 'Specified Price'Total # Stores
      Axyzx+y+z
      Babca+b+c
      Cklmk+l+m

       

      I want to express these values (x, y, z etc.) as distinct count of number of stores that satisfy the pricing criterion both as actual number of stores and % of stores.

       

      I think this is a simple problem but I'm a beginner and can't seem to crack it. I've tried calculated fields and table calculations but clearly not being able to crack it.

       

      Appreciate if someone can help me please.

       

      Many thanks in advance!

       

      Attaching a twbx and excel file with sample data. Thanks

        • 1. Re: Question on Distinct Count based on a specific condition
          Tom W

          COUNTD(IF [Price] < [Ideal Price Point] THEN [Store ID] END)

          • 2. Re: Question on Distinct Count based on a specific condition
            Elavar sevvel

            It would be good, if you can share the twbx file or the data sources in excel so that we can connect it to the tableau and execute and show you the  results.

            • 3. Re: Question on Distinct Count based on a specific condition
              Suvojit Basu

              Hi Tom... Thanks but unfortunately this formula didn't work. I tried a similar formula initially when I started working on this but I keep getting errors saying can't mix aggregate and non-aggregate dimensions...

               

              I'm uploading an excel file with a sample of the data structure and a twbx file with the same data. Hope this will help provide more clarity.

               

              Thanks.

              • 4. Re: Question on Distinct Count based on a specific condition
                Suvojit Basu

                I'm doing that right now. I'm uploading an excel file with a sample of the data table and a reference list with the 'ideal price'. Please note...

                 

                - The benchmark price could be the 'ideal price' from the list or 'average of all price points' for the product.

                - I'm using an older version of Tableau (8.2) just so you know.

                 

                Many thanks for helping me out. Hope now I'll get a solution soon!

                 

                Thanks All!

                • 5. Re: Question on Distinct Count based on a specific condition
                  Tom W

                  If you could please outline what you're trying to calculate relative to the workbook you've attached by referencing the actual field names within it, that would be helpful.

                  Also, if you could avoid using words like 'could' - please tell us how you actually want it calculated.

                  • 6. Re: Question on Distinct Count based on a specific condition
                    Marcia Dwaree

                    Hi Suvojit,

                     

                    I don't know if I understood properly what you'd like to do, but it is very easy to do with the Tableau. In the example bellow I want that the field returns to me the quantity of items with price lower that 10 and then you can repeat the same concept for all the operators = or >.

                     

                     

                    New field [Price < 10]

                    IF [Price] < 10 THEN 1

                    ELSE 0

                    END

                     

                     

                    I hope it can help you.

                     

                     

                    Cheers,

                     

                    Marcia

                    • 7. Re: Question on Distinct Count based on a specific condition
                      Suvojit Basu

                      Hi Tom and Marcia,

                       

                      Apologies for being unclear. The reason I used the word 'could' is because I 'want' to be able to calculate price variance in 2 ways.

                       

                      1. Calculation 1: For each [Product] (in the 'Data$' table), count number of stores [Store ID] where the product's [Price] ('Data$' table) is "less than", "equal to", and "greater than" [Ideal Price] ('Ideal Price$' table).

                      2. Calculation 2: For each [Product] (in the 'Data$' table), count number of stores [Store ID] where the product's [Price] ('Data$' table) is "less than", "equal to", and "greater than" 'Average' [Price] for the product across all stores.

                      3. The outcome has to be a text table with [Product] in rows and 3 columns - Number of Stores where Price is 'Less Than' Benchmark; Number of stores where Price = Benchmark, and Number of Stores where Price > Benchmark. The "Benchmark" here is either [Ideal Price] or Average [Price] for the product (points 1 and 2 above). Hence two variations of the calculation.

                      4. Lastly, this text table (I've tried to explain how it should look in my original post) needs to call out the number of stores as well as % of stores (in a separate table) i.e. 2 table with the same information expressed as actual number in one and % in another.

                       

                      I hope this clarifies the ask. Really appreciate any help that you can provide.

                       

                      Cheers!

                      • 8. Re: Question on Distinct Count based on a specific condition
                        Suvojit Basu

                        Hi Marcia... Sorry for being unclear. I've posted a reply just now with details of what I'm trying to achieve. Hope this will make things clear.

                         

                        Thanks very much for your help.

                         

                        Cheers!

                        • 9. Re: Question on Distinct Count based on a specific condition
                          Tom W

                          Suvojit Basu wrote:

                           

                          1. Calculation 1: For each [Product] (in the 'Data$' table), count number of stores [Store ID] where the product's [Price] ('Data$' table) is "less than", "equal to", and "greater than" [Ideal Price] ('Ideal Price$' table).

                          This logic doesn't make sense.

                          If it's an AND condition, then it will never evaluate to true as it's logically impossible for a number to be less than, equal to and greater than another number. Similarly, if you change it to OR, it will always evaluate to true.

                           

                          Given that I'm not sure exactly what you want to do, here's an example demonstrating just the less than portion -

                          COUNTD(IF [Price] < [Ideal Price] THEN [Store Id] END)

                           

                          For number 2, you're going to need to use a level of detail calculation which isn't possible in versions of Tableau prior to version 9.

                          • 10. Re: Question on Distinct Count based on a specific condition
                            Suvojit Basu

                            Tom... it is NOT an AND condition. 'Less Than' is one condition, 'Equal to' is another condition, and 'Greater Than' is the third condition. And these 3 will be the 3 columns in the text table. Products will be in rows. Hopefully if you refer to the table in my original post, you'll know what I mean. Totally agree with you that if it's an AND condition then it doesn't make sense.

                             

                            Thanks again.

                            • 11. Re: Question on Distinct Count based on a specific condition
                              Tom W

                              It doesn't make sense when you chain them all together in the same sentence like that, please break it out as separate items.

                              With that being said, you should be able to amend the calculated field logic I provided in my previous post - just change the operator to > or =

                              • 12. Re: Question on Distinct Count based on a specific condition
                                Suvojit Basu

                                Thanks Tom! I will try out your suggestion. Coming from an expert like you, I'm sure it'll work.

                                 

                                Thanks a bunch again.