2 Replies Latest reply on Dec 29, 2016 2:14 AM by Fábio Rocha

    Newbie - Question similar to countif in excel

    Fábio Rocha

      Hello guys,

       

      First of all, I want to say I'm new to Tableau, data work and data viz for that matter. My background is IT administration and I'm taking a new challenge in life so please go easy on me

      Started three weeks ago and I'm currently picking up some already done work.

       

      I'm banging my head on what I though would be a fairly easy thing.

       

      As an example, I have this data:

      StoreWeekSales
      Store1Week10
      Store1Week10
      Store1Week20
      Store1Week20
      Store1Week321
      Store1Week39
      Store2Week1120
      Store2Week2100
      Store2Week350
      Store2Week340
      Store2Week310
      Store3Week115
      Store3Week216
      Store3Week345

       

      So, in Tableau I created one simple sheet like this:

      What I want to do is build a new table with Stores in Rows, one column with SUM(Sales), another column with the COUNT(Week)if SUM(Sales) per week >20, and another column with the AVG(Sales) per Week with Sales>20.

       

      Basically if this was in excel I would countif(b2:d2;">20"), etc to show the number of relevant weeks and then perform a simple average function.

       

      So the result should come something like this:

        

      StoreSalesRelevant WeeksAverage Sales per Relevant  Weeks
      Store130130
      Store23203106,7
      Store376176

       

      Can you help me out on what I need to build to make these calcs?

      Thanks in advance for your help guys.

      Cheers.

        • 1. Re: Newbie - Question similar to countif in excel
          Bobby Souza

          Hi Fábio,

           

          Great question. See attached for a workbook.

           

          Basically, we want to create a FIXED formula to give us the total sales per store per week. We can achieve this by using the formula:

           

          IF {FIXED [Store],[Week]:SUM([Sales])}>20 then 1 else 0 END

           

          Note: The FIXED formula is great to use when you'd like to aggregate data at certain levels/dimensions.

           

          Now that we have those weeks with sales > 20 per store (labeled [Relevant Weeks]), we can create another field using [Relevant Weeks] (formula above) to get the average.

           

          SUM(IF [Relevant Weeks] = 1 THEN [Sales] ELSE 0 END)/SUM([Relevant Weeks])

           

          Hope this helps. Cheers!

          1 of 1 people found this helpful
          • 2. Re: Newbie - Question similar to countif in excel
            Fábio Rocha

            Hi Bobby,

             

            Yeah, that worked! Thanks a lot! I managed to get a little more understanding about FIXED as well

             

            Just have an issue regarding this being a LOD expression and having 3/4 filters needed to be applied prior to this calc. Just turned them into context, but I believe this will have an impact on performance as I need to show them on the dashboard so the reader can "customize" the view... I'm gonna wrap this up and compare the difference in performance.

             

            Thanks once again