Tableau - Similar function to SQL Group by

1
votes

I have recently started using Tableau but am still very new to it. I don't know enough of the Tableau vocabulary to be able to find this answer through brute force googling. Hopefully some one here can help point me in a good direction.

I have a simple table of data, containing the columns and types: Date (Date), ID (Numerical), and Status (Text)

I want to be able to generate a report that has: Date, and Percent of the IDs on that date where the Status is "Complete."

In SQL I could do this by running a query along the lines of:

select date, (count(status like "Complete")/count(status)*100) from table
group by date;

Where can I look for this in Tableau? I tried creating a new Calculated measure in the sheet I am working on, but I couldn't find any built in functions that would add this ability. Thanks for any assistance.

1
tableau-api
The fields that follow the keywords “group by” in a SQL query should be treated as dimensions in Tableau to get the same effect – Alex Blakemore

1 Answers

6
votes

So this should be pretty easy. In your tableau screen(when in a sheet) on the left you will see a column which is broken into two. On the top are the "Dimensions"(these are the things you will organise your data on, usually strings or dates) and on the bottom are the "Measures"(these are integers).

So under dimensions you should see "Date" and "Status". In the Dimensions you should see "Number of Records" and "ID"(this is your variable, you can convert it to a dimension by right clicking and selecting that option).

So what you need to do:

  1. You just want status = complete, so drag your "Status" field into the Filter box(just to the right of the dimension column). You will get a pop up of the different types, select "Complete".

This has filtered your data source for only complete rows.

  1. Drag you "Date" field to the "Rows" shelf, on the top of the screen. This will default to the Year of the date.

  2. It will automatically go to a bar chart, change it to a table by selecting "Show Me" on the top right and select the table icon.

  3. Double click on your "Number of Records" measure. This will add it to your sheet. This will now be auto summed per year.

  4. To convert it to % of, right click on the measure in the Marks window(it should look like a green box "SUM(Number ....)"). Right click and select the option "quick table calculation", here select "Percentage of Total".

You will now have the % total per year for all records where status is equal to correct.

  1. (Optional) - To adjust the date dimension, right click on the "Date" Pill(its up the top in the rows shelf). Hear you can see different options for quarter, month, day. You can change it to any of these or a custom data format.