6 Replies Latest reply on Feb 10, 2015 6:06 AM by Tableau kumar

    Issue with the calculated field by using it as a filter

    mahi reddy

      Hi,

       

      I want to see the customers who was selled more than 1 item in a single day.so i have created the following calculated fields and used that calculated field in filter to see those customers.

       

               Untitled1.png   Untitled.png

      By using the date filter i am looking at the customers.I had selected only relevant values option as well for the date filter. Here the issue i am facing is:In the date filter it is showing all the dates where there was no customers in that dates.so, i want to see only the relevant dates in the filter.

       

      Please find the following attachment and look at both sheet1 and sheet2 and check january 1 2013 and january 2 2013 to get clear understanding about the requirement.

        • 1. Re: Issue with the calculated field by using as a filter
          Tableau kumar

          Find attached file, that includes list of customers who purchased more tan in 3 years, this process will help you to get your solution.

          • 2. Re: Issue with the calculated field by using as a filter
            mahi reddy

            Hi Kumar,

             

            Thanks for your qiuck reply.

             

            I don't want to see the who purchased in morethan 3 years.I don't want to see the dates for customers who was not selled morethan 1 item per day.so, i want to see only the relevant dates in the filter.

            • 3. Re: Issue with the calculated field by using it as a filter
              Jonathan Drummey

              @Mahi - See the attached. I made use of a computed Set in Tableau to generate the desired set of customers, then used that as a filter.

               

              Here's what I did to build the workbook:

               

              1) Created an "Order Date + Customer Name" calculated dimension with the formula STR([Order Date]) + ' ' + [Customer Name].

              2) Created a Set on that dimension using a Conditional filter with the formula COUNTD([Item])>1. This does a distinct count of the # of items per Order Date + Customer, and returns True only when there is more then 1 Item. This is a separate aggregation across the data.

              3) Put that Set on the Filters shelf. This defaults to only including those customers w/ more than one purchase per order date.

              4) Added an Order Date Quick Filter and set it to show only relevant values.

              5) Duplicated the fields in your view.

               

              2015-02-09 09_09_35-Tableau - I want to see only relevant dates in the filter.png

               

              This solution is preferable to the table calc solution for two reasons:

               

              1) Table calc filters occur so late in the pipeline that there's no such thing as a "relevant values" filter for them. What we see for the list of values in a table calc filter is affected by filters on dimensions & regular aggregate measures, not by other table calc filters.

               

              2) Table calculations are complicated to set up and maintain, as you've probably found out. By moving the original aggregation into a Set, we can avoid table calcs.

               

              Tableau v9 will duplicate the entire Set computation inside a Level of Detail calculation, {FIXED [Customer Name], [Order Date]: COUNTD([Item]) > 1} does the same thing and can be used as a True/False filter in a the view, or (just like computed Set) as a dimension so you could do things like cohort comparisons.

               

              @Kumar - I'm glad you're helping out here on the forums, and I like to encourage people to improve in their answering skills, so that's why I'm writing this next bit. Even with my experience with both Tableau and the forums I found your answer confusing. First of all because it wasn't using the data that Mahi had so helpfully provided, and secondly because it didn't actually answer Mahi's question. Thirdly, when it comes to table calculations, when answering questions we have to be *extremely* careful to be clear about what the translations between dimensions & measure are (if we're not using the original poster's data), as well as what dimensions are expected in the view. Mahi's view had two additional dimensions that were not present in yours, and it wasn't necessarily clear that the translation would be Customer Name->Product and Year(Date)->Item. Finally, although you provided instructions in a Word document, you didn't provide a packaged workbook that showed those results. For simple cases like "move pill X to Y Shelf" a set of instructions can be sufficient, however when it comes to table calculations where missing one step can turn the rest of the results into garbage having a packaged workbook to refer back to makes life much easier for users because they have more to work with.

               

              Jonathan

              • 4. Re: Issue with the calculated field by using it as a filter
                mahi reddy

                Hi Jonathan,

                 

                Thank you very much for your valuable help and for spending your valuable time to resolve this issue.

                 

                I'm very happy to say that the way of your explanation is excellent.

                 

                Mahi

                • 6. Re: Issue with the calculated field by using it as a filter
                  Tableau kumar

                  Hi Jonathan,

                   

                  Very Thankful you for valuable suggestion , I shared an idea to get the idea or near by solution, that workbook was prepared for other requirement. I assumed it will help Mahi to get near by solution & provoke to get the idea.

                   

                  Thanks & Regards

                  Laxman Kumar