6 Replies Latest reply on Oct 8, 2018 5:49 PM by Ken Flerlage

    Filtering a month column to show amounts less than

    Megan Talley

      Hello!  I'm a Tableau newbie, and I'm needing help with something that was really simple for me to do in Excel, however, i'm having a hard time figuring out how to do it in Tableau.  I have a cross tab report set up showing technicians sales by month (see image below).  I have Month name in the columns, technician name in the rows, and the sum of 'invoice gross amount' in the text.  I need my report to show all the technicians who had sales under $9,000 in the month of August.  So, in Excel, I would just filter the 'Aug' column to show only sales <$9,000.  I know that I will need to create a calculated field for this in Tableau, however, I can't figure out how to word the calculated field.

       

      Any help is much appreciated.  Thank you!Cross Tab Example.PNG

        • 1. Re: Filtering a month column to show amounts less than
          Ken Flerlage

          Tableau works a bit differently than Excel, so some things that seemed easy in Excel may seem difficult in Tableau at first, at least until you start to understand how it works under the covers. After that, I think you'll find that Tableau is very powerful and makes a lot of your data work much easier than Excel. That said, to do this, you'll need to create a calculated field that will give you August only. It would look something like this:

           

          If [Month]=8 THEN

               [Sales]

          End

           

          You can then take this field and drag it up to filters. It should change that filter to a SUM and will allow you to select anything greater than 9000.

           

          Note: If you can share your packaged workbook, then we can show exactly how to do it with your data.

          1 of 1 people found this helpful
          • 2. Re: Filtering a month column to show amounts less than
            Megan Talley

            Thank you so much for your quick reply. I'm trying really hard to get out of the Excel mindset while learning Tableau, but it's been a challenge.  I know it will get easier as I learn more about Tableau.

             

            So, I created a calculated field like the one you suggested and it worked.  However, when I add it to the filters and filter it to show all under $9,000, my other month columns disappear.  Is there a way to show the sales for the previous months, as well as, the Aug sales when using this filter?

            • 3. Re: Filtering a month column to show amounts less than
              jason.harmer

              Hi Megan!

               

              Hope you are enjoying your Tableau journey. The formula you are using won't be able to show other months as-is. However, because you want to see how it's doing comparative to the months next to it, would it be better just to add a color filter to highlight anything under 9,000? Then you can easily identify which people have under 9k across all months, and you can see how they are trending.

               

              something like [Sales}<9000 and adding that to the color section on the marks card.

               

              I know this is a little different than what you asked, but maybe a change of direction will help. Good luck!

              • 4. Re: Filtering a month column to show amounts less than
                Ken Flerlage

                Oh yes of course. We'll need to do something a bit more complex. Typing from mobile at the moment. When I get a chance  I'll respond with another solution, unless someone else beats me to it.

                • 5. Re: Filtering a month column to show amounts less than
                  Ken Flerlage

                  Megan, any chance you can share a sample of your data, even if it is just a mockup in the same structure? It's pretty difficult to answer this properly without seeing the structure of your data.

                  • 6. Re: Filtering a month column to show amounts less than
                    Ken Flerlage

                    Hi, just following up on this one. Let us know if we can be of any further assistance.