Not sure what you are aiming for but find my approach as reference below and stored in attached workbook version 9.3
a. Refdate: IFNULL([Term Date],today())
b. Delta Ref date in days: DATEDIFF('day',[Hire Date],[Ref date])
c. Delta Ref date in months: DATEDIFF('month',[Hire Date],[Ref date])
d. Delta Ref date in months: DATEDIFF('year',[Hire Date],[Ref date])
Delta Ref Date.twbx 9.2 KB
If you want an actual string of 1s, you'll have to generate a string of rows in which to load the 1s.
If you just want to calculate numbers, then check out what Norbert did.
Yes. Basically, you need one row per date (per person, if each row will count as 1 -- or you can roll it up).
There are a few ways you can do this:
- Use Data Densification in Tableau to fill in missing dates and some table calculations to carry forward the 1 values and add them all up. Pros: no need to adjust your source data. Cons: complex, brittle, hard to setup and maintain. I would not recommend this approach if you can do either of the following.
- Create a data source that gives you a row per date (per person). If you have the ability to write a custom script or cross join to table containing a row per date (or use Tableau 10's ability to cross-database join to an Excel file of dates - you'll have to include a value in both tables that give you a join on every row) then you'll have a data source that is fairly easy to use. Pros: easy to use data source, drag and drop in Tableau. Cons: little bit of setup, potentially untenable if you already have a large volume of data, because this will create a Cartesian product (but works well for smaller/medium sized data sets)
- Create a data source that contains every date and use that as your primary data source. Then blend to your existing data. Use table calcs to fill in values for dates between start and end. Pros: keep existing data structure Cons: complex (not as much as #1), hard to work with filtering (because of the blend)
#2 is probably the best approach (and is what you demonstrated with your Excel file). But it's always good to know some options!
Hope this helps!
Thank you for the help so far, but I see a few hurdles....
1. CountD will just provide me whole counts. But some people are not FT. So the CountD treats them as 1
2. If I just sum or count then that causes another situation....where each month is 1, but the year is 12 counts.
How do I fix this. See attached import of the data I presented in excel.....
Also, do I just create a "calendar" in excel of all the dates possible and join the calendar to the lines..... So for example, I've been here for 9 years, excluding leap days, 9*365 = 3,285 lines in the data set on the server. So if we have had exactly 200 employees employed for the last 20 years, I would have 200*365*20= 1,460,000 lines and thus adding 73,000 lines each year?? Or I could do monthly and have 9*12=108 lines for me.
Not a lot of data in the long run, but trying to get the concept.
Book1.twbx 21.9 KB