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.

isa.serah Nov 2, 2016 12:57 PM

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.

AJ AJ Nov 2, 2016 1:11 PM (in response to isa.serah)

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
Like Show 2 Likes(2)

Actions
2. Re: How can I split number of months to year and months.

Simon Runc Nov 2, 2016 1:38 PM (in response to isa.serah)

...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
Like Show 3 Likes(3)

Actions
3. Re: How can I split number of months to year and months.

isa.serah Nov 3, 2016 6:29 AM (in response to isa.serah)

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 Runc Sherzodbek Ibragimov AJ AJ
1 of 1 people found this helpful
Like Show 1 Likes(1)

Actions
4. Re: How can I split number of months to year and months.

isa.serah Nov 3, 2016 6:51 AM (in response to isa.serah)

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
Like Show 2 Likes(2)

Actions
5. Re: How can I split number of months to year and months.

Tableau kumar Nov 3, 2016 7:12 AM (in response to isa.serah)

Calculating Different between 2 Dates in terms of Years, Months, Days

Calculating No. of Days between 2 Dates In terms of Years, Months, Days
2 of 2 people found this helpful
Like Show 3 Likes(3)

Actions
6. Re: How can I split number of months to year and months.

isa.serah Nov 3, 2016 7:21 AM (in response to Tableau kumar)

Thats helpful piece of work.
Like Show 1 Likes(1)

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

isa.serah Nov 3, 2016 1:46 PM (in response to Simon Runc)

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
Like Show 1 Likes(1)

Actions
8. Re: How can I split number of months to year and months.

Simon Runc Nov 4, 2016 2:15 AM (in response to isa.serah)

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
Like Show 2 Likes(2)

Actions
9. Re: How can I split number of months to year and months.

isa.serah Nov 4, 2016 7:03 AM (in response to Simon Runc)

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
Like Show 1 Likes(1)

Actions
10. Re: How can I split number of months to year and months.

Simon Runc Nov 4, 2016 7:17 AM (in response to isa.serah)

hi Isa,

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

1 of 1 people found this helpful
Like Show 1 Likes(1)

Actions
11. Re: How can I split number of months to year and months.

isa.serah Nov 4, 2016 7:18 AM (in response to Simon Runc)

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
Like Show 1 Likes(1)

Actions
12. Re: How can I split number of months to year and months.

Simon Runc Nov 4, 2016 7:23 AM (in response to isa.serah)

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
Like Show 1 Likes(1)

Actions
13. Re: How can I split number of months to year and months.

isa.serah Nov 4, 2016 8:57 AM (in response to Simon Runc)

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
Like Show 1 Likes(1)

Actions
14. Re: How can I split number of months to year and months.

Simon Runc Nov 4, 2016 9:13 AM (in response to isa.serah)

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
Like Show 2 Likes(2)

Actions