1 2 Previous Next 27 Replies Latest reply on Nov 15, 2016 1:24 AM by Simon Runc

# How can I split number of months to year and months.

Hi ,

I have a scenario in which ,i have to calculate the time  a particular customer was with our company.I calculated the months he was with our company using datediff('month',dt cust added,today()).SO i got result as say for example 135 months.But I want to display as 11 years and 3 months.How can we do that?Simon Runc.Or is there any other easy way other than datediff to achieve the required result?

• ###### 1. Re: How can I split number of months to year and months.

Just create a new calculation as

1 Months = 0.0833 Years = 1 Month

135 * 0.0833=11.2455 is the years

and then you can do a split using Left function.

This conversion of 1 months to years has been calculated by multiplying 1 months by 0.0833 and the result is 0.0833 years.

2 of 2 people found this helpful
• ###### 2. Re: How can I split number of months to year and months.

...or try this one, which is a slight variation on AJ's version

STR(INT([Employment Length]/12)) +' Years '+ Str([Employment Length]%12) + ' Months'

btw % is the MOD function, which returns the remained from a division...it's a surprisingly handy little function!

3 of 3 people found this helpful
• ###### 3. Re: How can I split number of months to year and months.

Hi AJ and Simon,

Thanks for your quick response. I collaborated both of your ideas together to get my result.

Say number of months is 268  months.then it has to be(268/12=22.3333)ie  22 years and 3 months.

But when I do the below formula

LEFT(STR(INT(MAX([Calculation2])/12)),2) + 'years' +  STR(MAX([Calculation2]%12))+ 'Months'

I am getting as 22 years and 11 months.I am not getting the number of months as 3.% function is giving me 11..which is correct 3 or 11 months?If it is 3 then is there any way to get that?Simon RuncSherzodbek IbragimovAJ AJ

1 of 1 people found this helpful
• ###### 4. Re: How can I split number of months to year and months.

Sorry,It was my mistake.% function works perfectly.I checked with many customers and is works perfect.Thank You Simon Runc and AJ AJ for your help

thanks a lot again

1 of 1 people found this helpful
• ###### 5. Re: How can I split number of months to year and months.
2 of 2 people found this helpful

• ###### 7. Re: How can I split number of months to year and months.

Hi Simon,

I have yet another question.I want to  get average of total  count of customers  for a particular segment in different years.I created a calculated field like {Fixed [segment],[Date]:count([custname])}

and I when i pulled into row i took the average of it.To compare if my calculation is correct or not I created other sheet and just added referance line to get average.But both values are dfifferent.

I needed the average of total count as calculated field.Can you help me in that?Simon Runc

1 of 1 people found this helpful
• ###### 8. Re: How can I split number of months to year and months.

hi Isa,

When you say the average count of customers, do you want the average per week, per month, or per year?

Depending on the answer to this, you need to define this in the LoD...so say you wanted the average per month...it would be

{Fixed [segment],DATETRUNC('month',[Date]):countd([custname])}

and then take the average of this.

I've also changed the count, to a countd...it might be you only have one row per customer, in which case count would work fine, but in my experience there is normally more than one row per customer (eg superstore, is at the grain of Date/Customer/OrderID/Product.

1 of 1 people found this helpful
• ###### 9. Re: How can I split number of months to year and months.

Hi Simon,

These are the values when I get when I add reference line  to take average.

But when I do it as calculation I get the value as.{Fixed [segment],[Date]:count([Rga Num])}

I am looking to get the average  of  count of returns made by all customer in a particular segment in these years.I tried with datetrunc inside fixed also,but no luck.Can you give me any suggestions to figure out why its happening like that?

1 of 1 people found this helpful
• ###### 10. Re: How can I split number of months to year and months.

hi Isa,

What is the dimension on the X-Axis on your Bar Chart?

1 of 1 people found this helpful
• ###### 11. Re: How can I split number of months to year and months.

Thats the customer name.There is a quick filter in the sheet  for segment also.I have selected a particular segment.

1 of 1 people found this helpful
• ###### 12. Re: How can I split number of months to year and months.

So if you change the calculation to this...

{FIXED [segment]: AVG({Fixed [Customer],YEAR([Date]):count([Rga Num])})}

It shouldn't matter if you bring this field in as an AVG/MIN/SUM....

You also won't need the YEAR() wrapper, if the final detail (as you have in your table is at year...else you'll need to specify the Date-level you want the calculation run over.

Does that return the same result?

1 of 1 people found this helpful
• ###### 13. Re: How can I split number of months to year and months.

Soory Simon,I was in meeting.

No..I am getting the above result.

Is there any way to see  the Underlying formula that shows  how tableau calculates average?If we figure out to do,it would be a great achievement for our team.

1 of 1 people found this helpful
• ###### 14. Re: How can I split number of months to year and months.

Not really (unless you connect it live to the data sources, and use the performance recorder, where you can then see the SQL that Tableau is sending to generate the results...so you'd also need a pretty good grasp on SQL)...the Reference Line calculation's are very much like doing a WINDOW_AVG Table Calc...but as you don't want to have customer in the VizLoD (you want that value to work, off-canvas), we want to recreate the behaviour with a LoD. If you like you can email (my email is on my profile) me over the file (if you are not able to post it here), and I'll then be able to decipher the difference (eg. I can't see if any filters are applied, how nulls are handled, anything else in the VizLoD...etc.)

2 of 2 people found this helpful
1 2 Previous Next