9 Replies Latest reply on Nov 2, 2016 6:23 AM by Giovanni Artavia

    Dynamic Quarters analysis based on static condition

    Giovanni Artavia

      Hello,

       

      I have a table that shows QoQ% growth.

      I need to find out a way to count the quarters with 'positive' growth.  The trick is:  if the agent tenure is 2, we need to analyze the last 4 quarters (16Q1-16Q4), if tenure=4, then analyze last 8 quarters (15Q1-16Q4), if Tenure = 12, then analyze the last 12 quarters. 

       

      In the example as the tenure=2, we will only count the last 4 quarters, so positive quarters= 2; and for Agent 3, as tenure=4, we need to analyze last 8 quarters, so positive quarters= 3

       

      I was able to identify with a '1' the positive quarters, and '0' Else, but I don't know how to sum the right quarters based on the condition... 

      Quarters are base on a date field, and tenure is a calculated field from a different datasource (excel)

       

      Thank you in advance for your support!

       

      Rep NameTenure (calculated field)15Q115Q215Q315Q416Q116Q2
      16Q316Q4

      Agent 1

      215%16%-5%-20%30%35%-10%

      -1%

      Agent 22
      Agent 34-5%15%16%-20%-30%-3%2%-15%
      Agent 412
      Agent 512
        • 1. Re: Dynamic Quarters analysis based on static condition
          Carl Slifer

          Hi Giovanni,

           

          I'm thinking you'll always show the last 12 quarters but you can have null values in some cells. These null values would be the quarters you don't want to see per tenure.

           

          IF [Tenure] <=2 THEN
               IF LAST() <=3 THEN (SUM([Value]) - LOOKUP(ZN(SUM([Value])),-1) )/  ABS(LOOKUP(ZN(SUM([Value])),-1)) END
          ELSEIF [Tenure] = 4 THEN
               IF LAST() <=7 THEN (SUM([Value]) - LOOKUP(ZN(SUM([Value])),-1) )/  ABS(LOOKUP(ZN(SUM([Value])),-1)) END

          ELSEIF [Tenture] = 12 THEN
          IF LAST() <=11 THEN (SUM([Value]) - LOOKUP(ZN(SUM([Value])),-1) )/  ABS(LOOKUP(ZN(SUM([Value])),-1)) END

           

          Excuse syntax as I don't have tableau open for data that would work like this very well and not sure if your tenure is aggregated or not but I think the concept should help. Is there a programmatic way of determining how many quarters? MIN([Tenture]*2,12) ? You take the smallest value, either 12 or two times their tenure? IF SO....

          IF LAST() <=(MIN([Tenure]*2,12) - 1) THEN (SUM([Value]) - LOOKUP(ZN(SUM([Value])),-1)) /  ABS(LOOKUP(ZN(SUM([Value])),-1)) END

          might be more succinct?

           

          got data?

           

          Carl Slifer

          InterWorks

          2 of 2 people found this helpful
          • 2. Re: Dynamic Quarters analysis based on static condition
            Carl Slifer

            Hi,

             

             

            I've taken the liberty of building out the example I missed a set of brackets in my calcs. Will edit them above as well.

            • 3. Re: Dynamic Quarters analysis based on static condition
              Giovanni Artavia

              Thank you very much for you help!

               

              I feel closer to the final solution now!  There is still something happening, because I cannot replicate the behavior on my workbook.. I am trying to create an extract that can be handle in public... Let me try to bring you more details.

               

              [Tenure (Years)] is a field that comes from an excel file, it is a numeric value, that shows the exact years on service by agent.

               

              [TENURE_TOTAL] is a calculated field that shows the threshold, I changed it to reflect the right buckets, I mean if the agent tenure is more than 4 years, we need to analyze 12 quarters, etc...

               

              [TENURE_TOTAL]

              IF ATTR([Tenure (Years)])>= 4 THEN "12"

              ELSEIF ATTR([Tenure (Years)]) >= 3 THEN "8"

              ELSEIF ATTR([Tenure (Years)]) >= 2 THEN "4"

              ELSE "IGNORE"

              END

               

              The error that I am receiving at the end is: UKNOWN  FUCTION SUM CALLED, it is showed in the last screenshot below.

               

              Thank you very much for all you support... I really appreciate it...

               

              • 4. Re: Dynamic Quarters analysis based on static condition
                Carl Slifer

                Hi Giovannni,

                 

                Mobile so much harder to evaluate in detail and reply. It appears you are using strings. This is why the SUM() function won't work. I imagine if you click the red text in the bottom left hand corner of the calculated field it will tell you as much.  In your calculated field do not declare the function like THEN "4" forgo the marks. Just 4. And you may then notice on  your pane it will switch from a dimension to a measure. Top left to bottom left. This will also most likely mess the view up until you replace it. If you want it as a string change it in the calc by wrapping an INT() just around the orange bit thats your tenure total quarter.

                • 5. Re: Dynamic Quarters analysis based on static condition
                  Giovanni Artavia

                  THANK YOU!

                   

                  Almost there!!  I made this modifications:

                  TENURE_TOTAL_QTR

                  Role:

                  Discrete Measure

                  Type:

                  Calculated Field

                  Status:

                  Valid

                  Formula

                  IF ATTR([2016 OTE_Terms AMER (August OTE Report for Sales Ops)].[Tenure (Years)])>= 4 THEN 12
                  ELSEIF ATTR([2016 OTE_Terms AMER (August OTE Report for Sales Ops)].[Tenure (Years)]) >= 3 THEN 8
                  ELSEIF ATTR([2016 OTE_Terms AMER (August OTE Report for Sales Ops)].[Tenure (Years)]) >= 2 THEN 4
                  END

                  Basically removed the " characters as per your suggestion...

                  Lastest sum try

                  Role:

                  Continuous Measure

                  Type:

                  Calculated Field

                  Status:

                  Valid

                  Formula

                  IF LAST() <=(MIN([TENURE_TOTAL_QTR]*2,12) - 1)
                  THEN (ZN(SUM([DETAIL_TOTAL_ADJUSTED])) - LOOKUP(ZN(SUM([DETAIL_TOTAL_ADJUSTED])), -1)) / ABS(LOOKUP(ZN(SUM([DETAIL_TOTAL_ADJUSTED])), -1))
                  END

                  and here I removed the sum() function after the MIN...  because I was receiving an aggregation error.. but I guess that is causing the error in the buckets... 

                  Screenshot (17).png

                   

                   

                  With those changes, this is how it looks the data... the calculations are fine, but cannot achieve the right quarterly buckets..

                   

                  Screenshot (19).png

                  • 6. Re: Dynamic Quarters analysis based on static condition
                    Carl Slifer

                    This sounds like an order of operations issue at this point.

                     

                    Can you please provide data that's a mock up? A worksheet with the people

                    and their tenure and another worksheet  with quarter values per person?

                     

                    On Tuesday, 1 November 2016, Giovanni Artavia <tableaucommunity@tableau.com>

                    • 7. Re: Dynamic Quarters analysis based on static condition
                      Giovanni Artavia

                      Hello Carl!

                       

                      I tried to create a muck up with the data, and put it in a excel file.  Basically the excel contains the raw data, and then the steps that we are trying to replicate in tableau.

                       

                      I hope it will make sense to you, otherwise please let me know..  Perhaps we can set a short call... if you prefer..

                       

                      I really appreciate all the support and patience!!

                       

                      Best Regards,

                      • 8. Re: Dynamic Quarters analysis based on static condition
                        Carl Slifer

                        Hi Giovanni,

                         

                        I do not take calls from work in the forums. This is something free. We help people here out in the open so that others can benefit from the free time we give out.  I work for a consulting firm and while I'm happy to take calls - these are paid only.  I hope you can understand that distinction and reasoning. That aside, please see the attached workbook.

                         

                        In the early bit you said tenure came from a different data source. I assumed blending and built around that.  Below is the calculation I've used. The only part that should be confusing at this bit is the first line so lets investigate it from the inside out.

                         

                        IF LAST() <=(MIN(MIN(INT([Tenure]))*2,12) - 1)

                        THEN (ZN(SUM([Total])) - LOOKUP(ZN(SUM([Total])), -1)) / ABS(LOOKUP(ZN(SUM([Total])), -1))

                        END

                         

                        INT([Tenure]) - This changes 1.5 to 1 and 2.083333 to 2 and 11.5 to 11 and so forth. Alternatively you could round here as well and probably could even use Ceiling - but not going to test that.

                        MIN(INT([Tenure]) - This returns the minimum INT([Tenure]) per partition. A partition is formed by having blue fields in the few. It is roughly saying returning the minimum of what and we said return the minimum tenure for each [Nal Rep Name]

                        (MIN(MIN(INT([Tenure])*2,12) -1) - This takes the value of the minimum integer from above and multiplies it by two. (1.5 will now become 2 and 2.333333 will be 4). The Next minimum finds the lowest value between that expression or 12. (If someone's tenure was over 6 then they'd still only show the last 12 quarters)  This value is subtracted by 1 to count for the LAST() function beginning at 0 for the most recent quarter.

                         

                         

                        Best Regards,

                        Carl Slifer

                        InterWorks

                        2 of 2 people found this helpful
                        • 9. Re: Dynamic Quarters analysis based on static condition
                          Giovanni Artavia

                          Carl!

                           

                          THANK YOU!!  I don't have words!  for all your support and for taking time to explain!!

                           

                          This works perfectly !  Really appreciated it. !!!

                           

                          Best Regards,