So hopefully this is what you were after...
so first I created a parameter, so you can choose where the black line goes...setting this up as a reference line.
I then created the following calculated field
[Count Active Documents from Selected Date]
IF [Select Date]>=[Start] AND [Select Date]<= DATEADD('day',[Duration],[Start]) THEN [Document Name] END
So this returns the [Document Name] if it's active during the selected date (else it returns NULLs which don't get counted in a COUNTD)...and then I can add both sheets to a dashboard...
Hope that makes sense, but please post back if not.
Count Active Documents.twbx 30.5 KB
thanks for the quick response.
Your answer solves half of my problem. Now I can see - one by one - how many documents are active.
But would it also be possible to plot this on a time series plot? So I can see the evolution of active documents over time (instead of adjusting the parameter one by one)
...OK well that's quite a different problem!! I'll have a think (although it may well require us to delve into the murky world of data densification!!...and even then I'm not 100% sure I'll get there)
Just so I know the tools at my disposal...do you have any control over the data source? as in are you able to re-shape/cross-join/custom SQL...etc. the data (and if so what is the datasource? I assume you are using a TDE, but where is the data refreshed from? Excel/SQL Server..etc
I'll try to think of a way to get it done from the existing data, but if I can't (or the solution is un-workablely complicated) good to know if I have any other options!
I figured it out myself. I made a join via a dummy dimension to an extra sheet which lists all the dates from 2012-2017 in the dimension [Date].
So per document I can then calculate a score:
if [Date]<[Start] or [End]<[Date] then 0 else 1 END
See the workbook attached.
Gantt chart_2.twbx 46.2 KB
Great stuff...and thanks for coming back on this.
This why I had all the datasource manipulation questions!...changing the data is by far the easiest (and best) solution!!
It can be done without modifying the source and instead using data denitrification as Simon Runc pointed out. However, modifying the source is definitely: 1) easier to maintain, 2) less brittle, 3) less prone to error, 4) less restricted (notice that the dates in the view below do not extend past the last Start date)
I've attached a workbook to demonstrate the densification approach and I'd be happy to go into details if anyone is interested.
Gantt chart.twbx 28.0 KB
Joshua...that is a beautiful solution!..the date carry forward calculations are particularly elegant. Agreed though that modifying the source is the best solution (there is also the limitation here that we can't densify the field beyond the MAX StartDate, and rightly so...if Tableau did, when would it stop)
Thanks for sharing
As a former microbiologist,
I particularly like your
You made my day, thank you!
...and you get concerned by your English!! - I have been (supposedly) speaking it for 39 years, writing it for 34...and I only know (and have ever known) 1 language!! (...although I was taught French at school, and do try to speak it when I visit France, I wouldn't consider it another language that I know...and I'm sure neither would any French speaker)
...for other typo mistakes, you'll also love this thread Re: ...this made me laugh
Not only do I point out (and find funny) someone else's Typo, I then go on to realise I've a typo on the word 'Error' (of all words) in my IM greeting ...this is why I like numbers!!
Honestly, sometimes it feels like dentrification!
Thanks for the link Joshua...I hadn't seen that one BIG +1 from me