
1. Re: Dynamic Quarters analysis based on static condition
Carl Slifer Oct 31, 2016 9:03 AM (in response to Giovanni Artavia)2 of 2 people found this helpfulHi 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)) ENDELSEIF [Tenture] = 12 THEN
IF LAST() <=11 THEN (SUM([Value])  LOOKUP(ZN(SUM([Value])),1) )/ ABS(LOOKUP(ZN(SUM([Value])),1)) ENDExcuse 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

2. Re: Dynamic Quarters analysis based on static condition
Carl Slifer Oct 31, 2016 9:02 AM (in response to 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.

TenureTableCalc.twbx 10.2 KB


3. Re: Dynamic Quarters analysis based on static condition
Giovanni Artavia Oct 31, 2016 1:05 PM (in response to Carl Slifer)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 Oct 31, 2016 1:17 PM (in response to Giovanni Artavia)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 Nov 1, 2016 8:09 AM (in response to Carl Slifer)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
ENDBasically 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))
ENDand 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
Carl Slifer Nov 1, 2016 11:02 AM (in response to Giovanni Artavia)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 Nov 1, 2016 12:58 PM (in response to Carl Slifer)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,

Qtr Book Mock Results.xlsx 46.4 KB


8. Re: Dynamic Quarters analysis based on static condition
Carl Slifer Nov 2, 2016 1:55 AM (in response to Giovanni Artavia)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

QuarterTableCalc.twbx 60.2 KB


9. Re: Dynamic Quarters analysis based on static condition
Giovanni Artavia Nov 2, 2016 6:23 AM (in response to Carl Slifer)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,