2 Replies Latest reply on Nov 1, 2018 10:13 AM by Aleksandra Shander

    Create calculation/union statement for two sets of dates against one primary key

    Aleksandra Shander

      I will say in advance that this community ROCKS and folks are always willing to help even when not enough details are provided by a question submitter. I am unable to include packaged workbook because it contains privileged data, so appreciate in advance anyone who attempts to respond despite this challenge. I REALLY need your help.


      I am trying to do something that I've Googled extensively (and read quite a few Tableau Forum posts about related subject) but haven't found a way to make what I need happen. I need to be able to create two related, but not identical, vizzes:


      1. # of incidents submitted and how many of those are closed, by month. For example, 100 incidents submitted, 95 of those closed in September.

      2. # of incidents submitted and # of incidents closed, by month. For example, 100 incidents submitted, 110 closed in September.


      I need to be able to visualize this by month, but have the option to retool this by owner/product line/etc.


      The data rows in my SQL database are structured like this:


      Incident ID * Customer * Title * Product Line * Incident Type * Incident Status * Incident Owner * Date Submitted * Date Closed * Product Area * Product Component RWS408629 * A * ABCD * UVW * Implementation Task * Closed * Johann Strauss * 10/29/2018 * 10/29/2018 * Area A * Component A

      RWS408617 * B * EFGH * XYZ * Support Task * New  *Arvo Part * 10/29/2018 * Area B * Component B


      Additional requirement:

      I need to be able to use existing groups/filters that are in use by other vizzes for the same data source (SQL connection to a single table) - the main solution I've found that seems most relevant is this one: Model tickets open vs closed but it seems to presume that the only thing you want to visualize is this and I don't understand how to set up the custom SQL in addition to the main query that shows up when I open the Custom SQL window, which is just a list of all the columns in the table with aliases for each column. If I replace it, I lose my main data source for a bunch of vizzes; I tried appending to the end, and it gave me an error; so I'm just at a loss.


      I've written the below SQL query, which works to create two independent data rows, one for createddate, and one for closeddate, with customdate as the field, to attempt to make the solution in Model tickets open vs closed work but have the issue called out under "Additional requirement" above.



      [IncidentID], productline, productarea, owner, customername, createddate as CustomDate from dim.incident with (nolock)

      union all

      [IncidentID], productline, productarea, owner, customername, closeddate as CustomDate from dim.incident with (nolock)

      order by incidentid, customdate