4 Replies Latest reply on Jun 13, 2016 12:18 PM by Carlos Granados

    Calculated field which variables depend on an active filter

    Carlos Granados

      Good morning,

       

      I need help with the following idea:

       

      I have created the following "calculated field":

       

      ((SUM(IF [Membership_Type] = 1 AND [Rank by DogID] = 1 THEN 1 ELSE 0 END))

      -(SUM(IF [Membership_Type] = 1 AND [Rank by DogID] = 20 THEN 1 ELSE 0 END)))

      /(SUM(IF [Membership_Type] = 1 AND [Rank by DogID] = 1 THEN 1 ELSE 0 END))

       

      Which does the following calculation, for example:  (9000-3000)/9000 = 0,666  This allow me to calculate the difference in percentage between an interval.

       

      But as you can see in the formula the "Membership_Type" variable is fixed and the "Rank by DogID" variable is also fixed, so the interval is fixed and I cannot change it.

       

      Can the value of those variables be set up using for example a filter, this will allow me to change the interval dynamically without having to change the formula every time that I want to change that interval.

       

      I hope my explanation is clear enough.

       

      Thank you in advance.

        • 1. Re: Calculated field which variables depend on an active filter
          Tina Hauser

          Hi Carlos,

           

          You could create parameters and for these values and insert the parameter into your calculation instead of the fixed value.

           

          If your membership type parameter was named Membership_Type_P, for example, your calculation would look like:

          SUM(IF [Membership_Type] = [Membership_Type_P]

           

          Do you have a sample workbook you are able to share? Happy to take a closer look if this isn't the solution you were looking for.

          Tina

          • 2. Re: Calculated field which variables depend on an active filter
            Carlos Granados

            First of all thank you Tina,

             

            I checked that option but I cannot set up parameters, is it because I have free Tableau Public Software ? So I do not have all the options available ? 

             

            This is my "Create calculated field screen" as you can see I do not have the option parameters.

            create calculated field screen.png

             

            Thank you in advance

            • 3. Re: Calculated field which variables depend on an active filter
              Vincent Baumel

              It doesn't have anything to do with your using Tableau Public, so that's good news!

               

              I think what Tina's getting at is that you could create parameters, not create calculations. Click the small arrow alongside "Dimensions" on the left, and select "Create Parameter". Based on what you've said, it sounds like [Membership_Type] and [Rank by DogID] are both numeric fields. In the Create Parameter dialog box, name your first parameter something like Membership_Type_Prm and select "Data type:integer". Then select "Allowable values: List" and hit "Add from Field" to find the Membership_Type dimension. Once you select it, your list will be built using those dimensions as possible parameter choices. Follow a similar procedure for DogID. Now you can use these parameters in your calculations! The value of the parameter would be set to whatever the user selects. This saves you having to have much more complicated calculations. If you could post a mockup of your data set we could help set you in the right direction.

              • 4. Re: Calculated field which variables depend on an active filter
                Carlos Granados

                Thank you two for your advice, I finally managed to set up what I wanted.

                You were both right, it is the parameter option what I need.

                 

                I just couldn't find where was the parameter option, because of the following picture I though that parameters where within the "calculated field option":

                 

                create calculated field screen.png

                 

                This is how I set up everything and it is just what I wanted:

                 

                create calculated field screen.png

                 

                Thank you again !!!