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

# Dynamic Quarters analysis based on static condition

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)

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

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

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

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

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

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: ValidFormula

IF LAST() <=(MIN([TENURE_TOTAL_QTR]*2,12) - 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...

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

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

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

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

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

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,