5 Replies Latest reply on Jul 28, 2017 2:11 AM by Simon Runc

    how to count unique SKU number when sales is greater than 0?

    April Li

      Hi masters,

      I am trying to calculate the number of unique SKUļ¼Œ when SKU sales is greater than 0?

      The formula I believe i should use is countd, however there is some weird results returned.

      Anybody who can help to correct me please?

      Step 1: Create a SKU count with below formula:

      if sum([sales])>0

      then countd([Product])

      ELSE 0

      END

      Step1.JPG

       

      Step 2: however, In April 2017, my raw data behind has 120 lines, and on the added calculated field for each line, the returned value is correct. but when it adds up in the table above, it still shows all the SKU count regardless whether the sales is 0 or greater than 0.

      Step2.JPG

      how do i get the graph to return only unique SKU count with sales greater than 0?

        • 1. Re: how to count unique SKU number when sales is greater than 0?
          Simon Runc

          hi April,

           

          I think you need to do the >0 test at row level, and then aggregate the results in a COUNTD

           

          So something like

           

          COUNTD(IIF(zn([Sales Volume])>0,[SKU Code],NULL))

           

          This way only rows with Sales Volume >0 will have a SKU Code, else it will be NULL and NULLs don't get counted in COUNTDs (the "zn" is just in case you have Null sales volume, which get equated to zero with "zn")

           

          Hope that helps, but let me know if that doesn't solve your issue

          1 of 1 people found this helpful
          • 2. Re: how to count unique SKU number when sales is greater than 0?
            Rohan Makhija

            Hi,

            Try this.

            countd(if sum([sales])>0

            then ([Product])

            END)

            • 3. Re: how to count unique SKU number when sales is greater than 0?
              April Li

              it works.. super helpful.. thanks!

              • 4. Re: how to count unique SKU number when sales is greater than 0?
                April Li

                I have a follow up question... when i try sum(sales)>0 as the condition, the formula does not work as the error message says"cannot combine aggregate and non-aggregate measures together"

                countd(iif(sum([Sales Volume])>0, [SKU Code],null))

                 

                what if i want count the SKU number only when sales is greater than a certain amount? says 100 dollars?

                and the 100 dollars is an aggregate of a few regions in the raw data structure?

                 

                hope i get my answers clear.

                • 5. Re: how to count unique SKU number when sales is greater than 0?
                  Simon Runc

                  Hi April,

                   

                  So when you use an aggregate (such as SUM) Tableau needs to know at what level you want to aggregate the data at (is it SUM of Sales by SKUs, SUM of Sales by Region, SUM of Sales by Region/SKU...). So this is why you can't mix aggregates and non-aggregates (I've written a post explaining this, and calculation types in general here which you might find useful).

                   

                  On the canvas the VizLoD (Viz Level of Details) tells Tableau what level you want the aggregate, so if you had SKU in the Viz it would calculate the SUM of Sales by SKU....but we don't necessarily want to have SKU in the Viz everytime...this is why Tableau created FIXED LoDs. These let you tell Tableau the level of aggregation for an aggregate and then return the result at row level (a little bit like SUMIFs in Excel), so they can be used as a row level test.

                   

                  So you could do something like...

                   

                  countd(iif({FIXED [SKU Code]: sum([Sales Volume])}>100, [SKU Code],null))

                   

                  So the FIXED LoD part

                   

                  {FIXED [SKU Code]: sum([Sales Volume])}

                   

                  is saying do SUM of Sales by [SKU Code]...and return that result to each SKU (at row level)...very cool!

                   

                  Hope that helps and makes sense.