Yes, I have looked into it, but it is a total different problem.
I need the count of the tickets for each day (even if not ticket was opened or closed at that day).
Is there nobody who know an answer?
I thinks that is a really standard type a common problem.
Tableau Support Team - Would you please answer?
These forums are not really part of the Tableau Customer Support process. It is a self-help area where people can ask questions and VOLUNTEERS can respond...which is anybody who is a registered user like you or me The number of actual Tableau employees is very, very small compared to the number of non-Tableau registered users and they cannot possibly answer everybody's questions. People are free to help whoever they want, whenever they want, if they even want to. No SLA (Service Level Agreement), no pressure. It is unofficial help and if the question is well worded, detailed, and [typically] includes a packaged workbook the response and probable solution is much quicker -- plus it's a great way to learn the product if you can help others! If you need official help then you need to contact Tableau Customer Support.
Here's one way to approach it. I used the Sample Superstore Data:
1. Add the date to the columns shelf:
2. Create the following formula and substitute your field names (Open & Tickets):
3. Add to the viz, change to Shape, and change the Table Calculation to compute using cell:
4. UNCHECK aggregate measures from the Analysis menu:
5. Now you have your count by day with one mark per count:
Let us know if this is what you're looking for.
Upfront! Not the final solution but an approach to be considered;) Find it below and stored in attached workbook version 9.3 located in the original thread based on scaffold Technic explained here
1. It starts with 3 files.
a. Scaffold file with all dates of the month
b. File with open dates (copy of original file)
b. File with close dates (copy of original file)
The scaffold file is primary. Edit relationships between Scaffold file and Closed Date based on Date-Close and Scaffold file and Open Date based on Date-Open.
Define calculated field: Open Tickets: if ATTR([Open Date].[Open])>=attr([Date]) then 1
elseif ATTR([Closed Date].[Close])=attr([Date]) then -1
else 0 END
Now the "dissatisfier";( Works perfect when Close Date>Open Date. On the other site...is it a realistic scenario when Close Date=Open Date;) a matter of granularity. When Close - en Open date hours are included then scaffold dataset should have dd.mm.yyyy hh:mm
Would like to ask Simon Runc to the table.
Simon Runc An easter-egg is still "bothering" me concerning Close Date=Open Date issue. Any suggestions;)?
scaffold scenario.twbx 36.0 KB
Thanks for your answer!
But it does not give the OPEN orders (laying between [Order Date] and [Ship Date]).
It also does not handle dates without orders.
thanks for your answer.
But I cannot use an scaffold file, because the time span is floating.
I'm wondering, why this simple scenario cannot easily be solved in Tableau.
So first a big shout-out for Walt's solution....I do like that...very clever!
So using the 3 rows that Juergen posted, and following the KB article it seemed to do the trick...
Hi Juergen, I've attached (in T10.0) a working version of the KB article...let me know if anything doesn't make sense, or it isn't doing what you want.
Open Close Tickets.twbx 25.0 KB
You can add the dates without orders by right-clicking on the date field and checking "Show Missing Values":
Then, you must modify the calculation slightly (change from WINDOW_COUNT to WINDOW_SUM):
Now you have order dates with zero orders:
As far as open orders, the dataset I'm working with doesn't have open orders. If you can post an example dataset, then I'm sure we can find a way to get the calculation to work correctly.
Just because you perceive it as simple doesn't mean it is. I can hop on a bicycle blindfolded and ride it down our driveway but my wife can't. She can't even ride a bicycle even though it's very simple to do for most children.
The fact that your data is rigidly set and you aren't allowed to massage it into a usable format does kind of limit what can be done. Remember, Tableau likes data that's ready to go with minimal-to-no ETL needed.
It looks like Walt got it but you need to add some more data per his request to help him out: Re: Amount of tickets every day
Thanks a lot - this solved the the problem of the missing dates!
The Sample Superstore Data has the field 'Ship Date'.
I want the amount of all orders worked on (ordered but not yet shipped) for each day.
Okay, that makes sense. So I would create a calculated field which essentially filters only Orders where the ship date was not on the order date:
Then, modify your calculation by substituting this field for [Order ID]:
I'm attaching the modified workbook for your reference.
I will echo what Toby Erkson had to say: all too often, somethign that should be 'simple' can be very complicated to do in Tableau, especially if you're trying to emulate something from an Excel workbook. Fortunately, the Tableau community is amazing and super responsive and helpful, so welcome to the club and we are glad you're here.
Thank you for your effort !!!
It's indeed a great community and I hope I could contribute by myself in the future.
But the result seems to be wrong.
It shows the same as the diagramm before.
Not the Open orders for each day, instead the amount opened each day (as before).
January 4th -> 4 tickets instead 3
January 8th -> 12 tickets instead 0