Can you share a sample workbook so we can better see the structure of the input data?
I've mocked up some data and taken a crack at your problem. Let me know if this is helpful or if I have misunderstood your requirements. I can explain in more detail what I've done after I determine if I'm on the right track or not.
Thank you for your reply.
My requirements for the report are different. I need to show the number of the open incidents for any single day during any period.
Please see attached excel spreadsheet. I changed your data in a Data tab and created a mock up report for the period of 11/7/2016 - 11/15/2016 in a Report tab..
Incidents_rpt.xlsx 13.2 KB
I understood that you want to select the Date Dynamically, then report should show the that date's relevant data, If it is right,
1) Drag [Date], [Incidents fort the Period] on to work area, then right click on [Date] either in Row shelf or Column shelf ( Wherever you place that).
2) Select "Show Quick Filter". It will show Year wise, If you want to see Date Wise then
a) Right click on [Date] on the work area, then select "Exact Date", then again right click then select "Discrete" option.
3) Now Report will show the selected Date's Incidents.
Alternate option is using Parameter,
1) Right click on "Date" then select "Create" then "Parameter". Then select "All" option.
2) Then create a calculated field as below images.
4) Now place the Date Field as well the above calculated field.
5) Now report will show Sales for selected Date ( [Parameter]).
6) It will show nulls for rest of the dates, Drag the Calculated field onto filters shelf, then select "Sum".
7) Select "Special" tab then select "Non-null Values".
Check out this article:
I'm confident that this is what you need to solve your problem. I would demonstrate this with your data but unfortunately Tableau Desktop for Mac doesn't seem to support Excel's legacy connector.
Kumar, thank you, but it's not what I need.
I need to have a graph or a table that can show any given date range and a number of open incidents for this particular date.
The solution you suggests shows a number of open incidents for one day at a time, not for a date range.
Muralidhar, i's very helpful article, thank you!
I created an excel table with a date field.
The problem is that my original data is a SQL server db, I can't join this data with excel data.
1 of 1 people found this helpful
How much control do you have on your SQL Server? If it is in your dev environment/sandbox here is what you can do:
- Create an Excel spreadsheet with the date range you will be using, e.g. Jan 1, 2014 to Dec 31, 2015 to get two years worth of dates where each row is a single date
- In SQL Server Management Studio (SSMS) right click on the database and choose "Import Data"
- Make sure you choose an Excel source and navigate to the excel file you created. This will quickly import all the data in to SQL Server
- Do the joins as outlined in the article
You will need to make sure this date dimension is in the same database as the tables you are using, at least until Tableau releases the cross database joins feature.
If you don't have permission to create your own tables, you'll have to request it, or you will have to export your dataset to Excel (yuck!) and just work that way.
Muralidgar, thank you very much!