Forum Discussion
Date Diff in custom visual/grouping
Hi All
i have a custom column which calculates the length of service for leavers (Month date diff between start and end date)
then have another column which then divides the above by 12 to get years of service, ie 1.33 - i did have these rounded to do full years of service in a visual
The requirement has now changed to look something like the below - i just wanted thoughts on how the most efficient way of dealing with, is it a case of a long IF statement to return a number sequence linked to a new custom table?
TIA
David
| Leavers | |
| 10 years + | 2 |
| 9-10 years | 1 |
| 8-9 years | 2 |
| 7-8 years | 3 |
| 6-7 years | 2 |
| 5-6 years | 3 |
| 4-5 years | 2 |
| 3-4 years | 5 |
| 2-3 Years | 5 |
| 1-2 years | 5 |
| 6-12 Months | 4 |
| 0-6 months | 5 |
Anonymous , You can create a bucket like, a new column
New column=
var _diff = datediff([start date], [end date], month)/12c
Switch(true() ,
_diff <= .5 , " 0 - 6 Month",
_diff <= 1, " 6 - 12 Month",
_diff <=2 , " 1 -2 Years",
// Keep on additing other
)
1 Reply
Anonymous , You can create a bucket like, a new column
New column=
var _diff = datediff([start date], [end date], month)/12c
Switch(true() ,
_diff <= .5 , " 0 - 6 Month",
_diff <= 1, " 6 - 12 Month",
_diff <=2 , " 1 -2 Years",
// Keep on additing other
)