8 Replies Latest reply on Dec 7, 2016 9:54 AM by Jillian Lellis

    Calculated Field help

    Jillian Lellis

      Hey guys,

      I need some help. Heres the scenario: typically i would export an excel document. add filters, then i'd sort on those filters. If there was a sales rep name in the first field, immediately, it would be a tier 1 customer. if there was a sales rep name in the second slot but nothing in the first slot, it would be a tier 2 customer. If the rep's name in slot 2 was equal to "HELEN" i would rename that name to "SSC" then i would unselect that prior to naming tier 2. then if the only sales rep assigned to the customer was "SSC" or blank, i would name those customers tier 3.  I know how to do this in excel, but i'm having trouble getting it to work in my tableau. I changed my data source, so i'm directly tapping into my CRM server now.


      This is what i have so far, but it's not working properly:


      IF [Sales Rep Type]='2' THEN "PSSR ASSIGNED"

      ELSEIF [Sales Rep Type]='32' THEN "RAM Assigned"

      ELSEIF ([Sales Rep Type]='32' AND [Last Name]='KRUG') THEN "SSC"




      Thanks in advance!


        • 1. Re: Calculated Field help
          David Li

          Hi Jillian! How is it malfunctioning? Do you get an error message? And is [Sales Rep Type] a numeric field or a string field? If it's numeric, you shouldn't put the quotes around the numbers.

          • 2. Re: Calculated Field help
            Jillian Lellis

            It works for the PSSR assigned part but it has turned all others into UNASSIGNED


            • 3. Re: Calculated Field help
              David Li

              Can you share a packaged workbook so I can take a look?


              Also, I noticed that in your code, "SSC" will never be assigned, because that condition will always be preempted by the one above it. You'll probably want to swap their positions in the code. I don't think that's the issue that causes all the UNASSIGNED values, though.

              • 4. Re: Calculated Field help
                Jillian Lellis

                This data is confidential so i cannot export it. But i did get the logic to work, except that if there are multiple reps assigned, now it pulls in both reps and duplicates the value. Help!


                IF [Sales Rep Type]='2' and [Name]<>' ' then 'Tier 1'

                ELSEIF [Sales Rep Type]='4' AND [Name]='KRUG' then 'tier 3'

                ELSEIF [Sales Rep Type]='4' and null then 'tier 2'

                ELSE 'tier 3'



                then i'm trying to do this

                if[teste]=1 and 2 or 3 then 'tier 1'

                ELSEIF [teste]=2 and 3 then 'tier 2'

                ELSEIF [teste]=3 then 'tier 3' 

                else "Tier 3"



                and it says i cant logically do that. hellllllllp!

                • 5. Re: Calculated Field help
                  Srinidhi Narayan

                  I would suggest that if you cant post your data, mock up some data that would make sense to you (via a coding system) but not to others.  For example use a generic dimension like "Color" or "Car" or something, and have some values for that, but internally you know that each value equates to a data value that you currently are dealing with.  What you have provided isn't sufficient to understand your exact problem, and it will only lead to further confusion in the minds of readers, giving you answers you either already know, or is meaningless in your context.


                  For example:  In the following evaluation, what is null on the third line?   My guess is it is [some dimension] = null?  You will need to use isnull([dimension]).   Just saying " and null " doesn't make sense.


                  IF [Sales Rep Type]='2' and [Name]<>' ' then 'Tier 1'

                  ELSEIF [Sales Rep Type]='4' AND [Name]='KRUG' then 'tier 3'

                  ELSEIF [Sales Rep Type]='4' and null then 'tier 2'

                  ELSE 'tier 3'



                  In the second expression... you have "if([teste]] = 1 and 2 or 3..."   that expression is ambiguous.. it isn't clear if you are testing the value, or testing the Boolean return of the values.   Even if you are just testing the Boolean return of 1 and 2 or 3... it isn't clear if it is "(1 and 2) or 3", or if it is "1 and (2 or 3)".


                  What are the possible values of [teste] in the way you have it worded?

                  • 7. Re: Calculated Field help
                    Jonathan Drummey

                    Hi Jillian,


                    I took a quick look at this over lunch and I think the challenge you are having is that the logic needs to work in two directions and the calculations have only been set up in one direction.


                    1) First of all there's the intra-record logic that needs to deal with different values of Sales Rep Type and Last Name for a given record.

                    2) Then there's the inter-record logic to resolve issues when there are multiple sales reps for a given customer. This requires some sort of aggregation and depending on the desired final output view could be accomplished in a variety of ways.


                    Here are some issues/points of confusion:


                    a) As defined the tier assignment is happening at a record level, it sounds like you want the tier assignment at a customer level. Is that correct?

                    b) Given a), what is the logic for assigning a tier at a customer level given all the combinations of values?

                    c) Given a) the dimension(s) that identify a customer haven't been identified. What field(s) uniquely identify a customer?

                    d) The tier calculation as written using using a [Last Name] <>' ' comparison, however in the sample data there are no Last Names with  ' ' as a value however there are Last Names with a value of Null. Should the comparison be checking for Nulls instead of a single space?

                    e) What is telling you that sales volumes are being duplicated given the calcs you are using? Do you have specific examples in the sample data?

                    f) Do you have an example of a desired final output?



                    • 8. Re: Calculated Field help
                      Jillian Lellis

                      answers to your questions:
                      a) Yes, it should assign at the customer level.

                      b) the logic is if there is a rep assigned in slot 2 (reptype=2) this is a tier 1 customer, regardless if there are other types of reps assigned to the customer. if there is no rep2, it needs to look at slot 4 (rep type=4 (except for last name krug)/ then this will be tier 2. then all unassigned to any slot, or assigned to reptype4, last name krug, should be tier 3.  (Does that make sense).

                      c) customer number identifies a unique customer. However, if the customer has two divisions, the logic should apply to each division separately. So i guess the real level would be unique customer number and division. for example: Customer 1 division g could have no rep 2 assigned and could be classified as a tier 3 customer but that same customer could have a rep 2 assigned to division E so it would be a tier 1 customer for that division (Make sense?)

                      d) Good point. Maybe i should change that to NULL. i didnt think of that.

                      e) I can tell the sales volumes are being duplicated when i nest the customer number in. I can see the same customer with two different tiers and the volume of sales is doubled. in some instances, there are 3 tiers and the volume is tripped. 

                      f) Not in tableau. I have an example in excel. The customers under each division need to be mutually exclusive.