Further to this I imagine I would also need something to say opendate > reportdate & closedate < reportdate to pick the ones open on a particular day.
1 of 1 people found this helpful
Not sure what your date situation is without seeing more info but in regards to your count a couple things need to be determined.
I see you're trying to use a countd which implies that you have repeating tasks and you only care about a rolled up state of each individual task?
This may not be the case, I'm just assuming.
If this were true then WorkOrderStatus would presumably be an attribute (not changing over each instance of the same task) Part of me feels like this isn't the case and countd is not what should be used here.
If a task is a unique record and no task repeats throughout your data then a calculation like below will work.
sum(if [WorkOrderStatus] <> 2 then 1 else 0 end)
(you can wrap in sum like above or just let Tableau apply the aggregation when placed on the viz)
If this is not the case, and there are multiple instances of the same task, and WorkOrderStatus is in deed an attribute
then a calc like this might be more applicable:
countd(if [WorkOrderStatus]<>2 then [TASK] end)
P.S. the latter should work in either scenario but might be a little more logic intensive.
Your initial assumption was correct however has pointed out the flaw in my thinking.
There are multiple records for each task, hence the use of countd, however the WorkORderStatus will change during the course of the task progress, therefore I cannot utilise this.
What I really need to do then is isolate the task entry for a specific date, thinking I might contact the vendor to see if they can help me out.
Thanks for your help.
What it sounds like you're looking for is a kind of census or queueing problem, where you want to count how many of X were active on a given date, only your data has a start date & stop date but not the in-between dates. To do that kind of analysis, we need to pad out the data, here's a link to a roundup of threads that address this, the cross join technique that Johan linked to is part of one possible solution.