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:



      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


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

      Thanks in advance for your help guys.


        • 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