You use a slighter older version of Tableau than I so you may have problems opening the attached, attached is 9.2.3 (Alternate Downloads Site | Tableau Software ).
My basic idea is to create the whole year view you want (of absence), get the latest month only for FTE, and then display it together in a dashboard.
I have written some instructions to guide -
The first thing I did was remove FTE from sheet 1, so it looks like this
Then create a new sheet, and a new calculation to always identify the latest month FTE
IF DATEPART('month',ATTR([Month]))= MONTH(WINDOW_MAX(MAX([Month]))) THEN SUM([FTE]) ELSE NULL END
This will return only December's FTE numbers.
I put this in a sheet on its own, like this - the trick is to add the department (as sheet1) but then hide it
Finally, add a dashboard, add sheet 1 and 2, and align them
The rest then is just formatting and positioning to end up with this (I colored it blue to make it more obvious)
Hope that helps! Or at least gives you some ideas
PS. Excuse me, but I recognize your name... do you work at STG Tooting?
FORUM ABSENCE EXAMPLE.twbx 226.9 KB
ha-ha, great minds!
Except I forgot LAST()
Hi Mark - yes I do, haha!
Thanks for the suggestions but I don't think the aligning is going to work unfortuntately. I have put a simple example to show what I am trying to do but on the actualy sheet I have lots of other filters by staff group etc. and aligning 2 dashboards won't actually work properly.
Hello Mark - i should have "i got this" but I didn't think you'd be on at this hour.
Bex - a dashboard will be the best option for controlling the view because otherwise you will get those repeating headers for each month for each measure.
It is simple enough to null out all the values with "If Last()=0 then Sum([FTE]) else null end" as another option.
and Bex - remember that you can have your filters apply over all the views in the dashboard. Typically a dashboard will provide a great deal more control and options while retaining flexibility. Maybe you could provide a few more details on the dashboard and additional parts of the view and we could help with that.
Hiya - I do use dashboards, I was just putting the data table up as an example. So when I use your formula, then I do just get December, but I get blank columns which I can't hide without affecting the absence data. So is the only option I have to align 2 dashboards? I know I can filter across dashboards but there are hundreds of departments/combinations and I am not confident they will always align. I could do some testing *grimace*
will some of the departments not exist for some measures? That would be the scenario which would/could cause problems because then the department will not appear at all. As long as each department has some piece of data then this will work. The nice part of using Last() to hide the other months in the view is that each measure will have 12 potential months to have data while only showing the most recent month.
You are right though - all the other months will appear for FTE if the single view approach is utilized.
Hi Mark - yes I do, haha!
I used to work there to... for about 7 years, nice place, great people
I worked in the information team, so I wouldn't have crossed over too much with HR apart from the monthly performance meetings.
Glad to hear all is well, and your using Tableau more across the organization... Genuinely - my last role there was to review & recommend BI software, I did Tableau and my colleagues did some competitors, I think you made a good choice
All the best!