2 Replies Latest reply on Sep 28, 2016 8:00 AM by Suvojit Basu

    How to COUNTD based on specific criterion?

    Suvojit Basu

      Hi,

       

      I'm doing some analysis based price audit conducted at stores. I want to create a table such as below.

       

      ProductReference TagAverage Price# Stores where Price < Avg. Price of 'Main' SKU (COLUMN 1)# Stores where Price = Avg. Price of 'Main' SKU (COLUMN 2)# Stores where Price > Avg. Price of 'Main' SKU (COLUMN 3)Total # Stores
      AMain$12.99324
      BReference$7.99600
      CReference$5.49500
      DReference$11.29625
      EReference$6.99900

       

      In COLUMN 1, I want to count the number of stores where the price of each product < average price of the 'main' product (Product A in the table below). There will always be ONE 'main' product. Similarly in COLUMN 2, count of stores where price of each product is = average price of 'main' product, and then in COLUMN 3, number of stores where price of the product is > average price of 'main' product (i.e. A in this table). I want to highlight that in these calculations, the 'price' is the actual audited price of the product in the store and not 'average price'. The average price column is just to show the average price of products in the market. The average price of the 'main' product is used as a 'Benchmark Price' for the calculations.

       

      I'm guessing that the formula should be something like this...COUNTD (IF [Price] < [Benchmark Price] THEN [Store ID] END).

       

      I created [Benchmark Price] as a calculated field using an IF statement (IF [Reference Tag]="Main" THEN [Price] END) and then created another calculated field using WINDOW_AVG to apply this 'benchmark price' across all products.

       

      The place where I'm stuck is that when I use the formula above COUNTD (IF [Price] < [Benchmark Price] THEN [Store ID] END), then it doesn't work as Tableau says I cannot mix aggregate and non-aggregate functions. How do I resolve this? Or how do I take a different approach to solve this problem.

       

      Your help is HIGHLY appreciated.

       

      Thanks in advance.

        • 1. Re: How to COUNTD based on specific criterion?
          Mahfooj Khan

          Its because you've already aggregated your [Benchmark Price].

          Try this and let me know If you've any query.

           

          IF SUM([Price])< [Benchmark Price] THEN COUNTD([Store ID]) END

           

          Mahfooj

          • 2. Re: How to COUNTD based on specific criterion?
            Suvojit Basu

            Hi Mahfooj,

             

            Thanks very much for responding to my question. Unfortunately the formula you suggested did not work. Just to give you a quick background, I had tried a similar formula myself except I used AVG instead of SUM.

             

            IF AVG([Response (Measure)])<[BM Price (Main for Tbl)] THEN COUNTD([LocationGUID (Measure)]) END... This was my formula. Whether I use AVG or SUM, I think what's happening here is that the formula is being evaluated at an aggregate level. For example (using my table in the original question), for Product B, the average price ($7.99) is less than the benchmark price (avg price of product A - $12.99) and so the formula is simply doing a COUNTD of all stores where product B was audited and showing that number under COLUMN 1 (Table above). What I really want is to count the total number of stores where the price of product B was < the average price of the 'main' product and show the number in COLUMN 1; same logic for COLUMN 2 and 3 in table above.. Your formula behaves in a similar manner and mine shown above.

             

            Just so you know, there is also another version of this table that I've created - the difference here is that the 'benchmark price' is hard coded and part of the data set. In this case, I used this formula there and it works perfectly.

             

            COUNTD (IF [Price] < [Benchmark Price] THEN [Store ID] END)... When I use this calculated field (named #Stores < BM) in my table, it shows as AGG(#Stores < BM) and works fine.

             

            In this new scenario where the 'benchmark price' is dynamic i.e. it is the average price of the 'main' product, I tried to use this formula with a small tweak COUNTD ( IF AVG([Price]) < [Benchmark Price] THEN [Store ID] END ) but it doesn't allow me and throws up the error message saying 'cannot mix aggregate and non-aggregate'.

             

            Sorry for the long reply! Wanted to explain the situation in detail. So the issue still remains unresolved... Please let me know if you have any other ideas.

             

            Thanks a ton!