2 of 2 people found this helpful
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?
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...
IF ATTR([Tenure (Years)])>= 4 THEN "12"
ELSEIF ATTR([Tenure (Years)]) >= 3 THEN "8"
ELSEIF ATTR([Tenure (Years)]) >= 2 THEN "4"
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...
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.
Almost there!! I made this modifications:
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
Basically removed the " characters as per your suggestion...
Lastest sum try
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))
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..
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 <email@example.com>
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!!
Qtr Book Mock Results.xlsx 46.4 KB
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))
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.
QuarterTableCalc.twbx 60.2 KB
THANK YOU!! I don't have words! for all your support and for taking time to explain!!
This works perfectly ! Really appreciated it. !!!