-
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)2 of 2 people found this helpfulJust 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. Re: How can I split number of months to year and months.
Simon RuncNov 2, 2016 1:38 PM (in response to isa.serah)
3 of 3 people found this helpful...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. 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)1 of 1 people found this helpfulHi 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
-
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)1 of 1 people found this helpfulSorry,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
-
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)2 of 2 people found this helpfulCalculating Different between 2 Dates in terms of Years, Months, Days
Calculating No. of Days between 2 Dates In terms of Years, Months, Days
-
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.
-
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)1 of 1 people found this helpfulHi 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
-
8. Re: How can I split number of months to year and months.
Simon RuncNov 4, 2016 2:15 AM (in response to isa.serah)
1 of 1 people found this helpfulhi 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.
-
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)1 of 1 people found this helpfulHi 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?
-
10. Re: How can I split number of months to year and months.
Simon RuncNov 4, 2016 7:17 AM (in response to isa.serah)
1 of 1 people found this helpful -
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)1 of 1 people found this helpfulThats the customer name.There is a quick filter in the sheet for segment also.I have selected a particular segment.
-
12. Re: How can I split number of months to year and months.
Simon RuncNov 4, 2016 7:23 AM (in response to isa.serah)
1 of 1 people found this helpfulSo 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?
-
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)1 of 1 people found this helpfulSoory 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.
-
14. Re: How can I split number of months to year and months.
Simon RuncNov 4, 2016 9:13 AM (in response to isa.serah)
2 of 2 people found this helpfulNot 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.)