Friday, September 30, 2016

Data Entry / Write Back in Tableau using Google Forms and Google Sheets

Here is a neat technique you can use to gather data right from inside your Tableau dashboard and write it to a 'database', which you can then connect to live to see real-time results.

Tableau is a read-only tool.  This is on purpose.  However sometimes there are scenarios where you might want to capture commentary, or host a poll/survey from inside Tableau, and you want to write that data back somewhere else.  Enter Google Forms and Google Sheets.  This technique works because Google Forms can accept entries via URL.  And Tableau and send URL actions which include dynamic inputs from your data.  Therefore we can post data to a Google Form from any Tableau dashboard to achieve 'write back' functionality.  I will walk through two use cases, but Im sure you will find many other creative ways of using this.

First step is to create your Google Form.  Create questions that are the same data type as what you want to have your users enter in tableau (numbers, string/text, etc).

For long Text input choose Paragraph:

And for numerical input you still choose text type of input, but choose Short Answer:


Note the unique ID of your form.  You will need this later when we make the URL Action in Tableau.

Right click in background and choose ‘View Page Source’:

Search for ‘entry’ and you will see them listed in order.  First occurrence is Question 1, next is Question 2, etc.  We will need to reference these when we create our URL action in Tableau.

Now let's set up a couple things in Tableau before we get to the URL actions (will need to make parameters and will need a sheet to act as the 'send' or 'submit' button).

Create a Parameter(s) in Tableau of the data type you want (string, integer, float, etc).  Show the Parameter(s) on your dashboard somewhere.  In this example I have a set of numerical parameters used for voting and another parameter used to capture comments (lets focus on the comment for now).

Create a new worksheet which uses the ‘Shape’ mark type and add a custom shape for your ‘Submit’ button.

Add this worksheet to your dashboard.

We will now create a URL Action to post the data to the Google Form.  Construct your URL so that it contains the Unique ID for your Google Form we found earlier in the browser, as well as all the entries/responses/inputs/parameters in it as well (separating them with ‘&’).  The form of the URL should look like this:

https://docs.google.com/forms/d/[UNIQUE_ID_OF_YOUR_GOOGLE_FORM]/formResponse?ifq&entry.[ENTRY_1_ID]=[INSERT_PARAMETER_YOU_WANT_TO_ENTER_FROM_TABLEAU]&submit=Submit
https://docs.google.com/forms/d/1irBqi5wLkhEnMGqM87o5dbSF7_m1Ia9Uj6QCKo2J86U/formResponse?ifq&entry.1379436949=&submit=Submit

If you need to submit multiple parameters/entries from this submission/click then separate them with ‘&’s and enter as many as you want:

…./formResponse?ifq&entry.[ENTRY_1_ID]=[PARAMETER_1]&entry.[ENTRY_2_ID]=[PARAMETER_2]&entry.[ENTRY_3_ID]=[PARAMETER_3]&submit=Submit


Add the Parameter values using the arrow on the side:



Then after you submit your first response you can return to the Google Form in the browser and you will see your first ‘comment’.  Click the Spreadsheet icon to create (and then see) the Google Sheet:



And now let's look at what the Numerical inputs for out beer voting parameters look like in the Google Sheet of Responses:

Awesome!  Now we can open a new Tableau workbook to connect to this Google Sheet and build whatever dashboards we want from it.  If you are using Tableau 10 just select ‘Google Sheets’ in the Connect To Data list:

In 9.3 and earlier you will need to use the Web Data Connector to connect to Google Sheets.  Go here and scroll down until you get to the Google Sheets Connector:



Now you have a live connection set up to the sheet of responses – create whatever you want with it!  Here is a simple example using the voting/poll results:



Credit to Dash Davidson who showed me this technique in his presentation at Tableau Conference 2015!

-The Don

Data Entry / Write-Back in Tableau using Google Forms and Google Sheets

Here is a neat technique you can use to gather data right from inside your Tableau dashboard and write it to a 'database', which you can then connect to live to see real-time results.

Tableau is a read-only tool.  This is on purpose.  However sometimes there are scenarios where you might want to capture commentary, or host a poll/survey from inside Tableau, and you want to write that data back somewhere else.  Enter Google Forms and Google Sheets.  This technique works because Google Forms can accept entries via URL.  And Tableau and send URL actions which include dynamic inputs from your data.  Therefore we can post data to a Google Form from any Tableau dashboard to achieve 'write back' functionality.  I will walk through two use cases, but Im sure you will find many other creative ways of using this.

First step is to create your Google Form.  Create questions that are the same data type as what you want to have your users enter in tableau (numbers, string/text, etc).

For long Text input choose Paragraph:

And for numerical input you still choose text type of input, but choose Short Answer:


Note the unique ID of your form.  You will need this later when we make the URL Action in Tableau.

Right click in background and choose ‘View Page Source’:

Search for ‘entry’ and you will see them listed in order.  First occurrence is Question 1, next is Question 2, etc.  We will need to reference these when we create our URL action in Tableau.

Now let's set up a couple things in Tableau before we get to the URL actions (will need to make parameters and will need a sheet to act as the 'send' or 'submit' button).

Create a Parameter(s) in Tableau of the data type you want (string, integer, float, etc).  Show the Parameter(s) on your dashboard somewhere.  In this example I have a set of numerical parameters used for voting and another parameter used to capture comments (lets focus on the comment for now).

Create a new worksheet which uses the ‘Shape’ mark type and add a custom shape for your ‘Submit’ button.

Add this worksheet to your dashboard.

We will now create a URL Action to post the data to the Google Form.  Construct your URL so that it contains the Unique ID for your Google Form we found earlier in the browser, as well as all the entries/responses/inputs/parameters in it as well (separating them with ‘&’).  The form of the URL should look like this:

https://docs.google.com/forms/d/[UNIQUE_ID_OF_YOUR_GOOGLE_FORM]/formResponse?ifq&entry.[ENTRY_1_ID]=[INSERT_PARAMETER_YOU_WANT_TO_ENTER]&submit=Submit
https://docs.google.com/forms/d/1irBqi5wLkhEnMGqM87o5dbSF7_m1Ia9Uj6QCKo2J86U/formResponse?ifq&entry.1379436949=&submit=Submit

If you need to submit multiple parameters/entries from this submission/click then separate them with ‘&’s and enter as many as you want:

…./formResponse?ifq&entry.[ENTRY_1_ID]=[PARAMETER_1]&entry.[ENTRY_2_ID]=[PARAMETER_2]&entry.[ENTRY_3_ID]=[PARAMETER_3]&submit=Submit


Add the Parameter values using the arrow on the side:



Then after you submit your first response you can return to the Google Form in the browser and you will see your first ‘comment’.  Click the Spreadsheet icon to create (and then see) the Google Sheet:







And now let's look at what the Numerical inputs for out beer voting parameters look like in the Google Sheet of Responses:
Awesome!  Now we can open a new Tableau workbook to connect to this Google Sheet and build whatever dashboards we want from it.  If you are using Tableau 10 just select ‘Google Sheets’ in the Connect To Data list:
 In 9.3 and earlier you will need to use the Web Data Connector to connect to Google Sheets.  Go here and scroll down until you get to the Google Sheets Connector:



Now you have a live connection set up to the sheet of responses – create whatever you want with it!  Here is a simple example using the voting/poll results:



Credit to Dash Davidson who showed me this technique in his presentation at Tableau Conference 2015!

-The Don

Friday, December 11, 2015

Cell Level Formatting in Tableau

I have to start this post be saying 'Please dont treat Tableau like Excel'.  They are different tools designed for different purposes.  Excel is a spreadsheet-making tool.  Tableau is a data visualization tool.  If this is your only use case for Tableau then you are doing it wrong.  This technique should be used only when absolutely necessary.

Cell-level formatting is a challenge in Tableau because whenever you introduce more than one measure into a text table you need to use the Measure Names and Measure Values fields.  They are like 'bucket' fields that contain all of your measures in one.  If you are confused by these read this KB article.

The problem with that is you only get one Marks card for Measure Values, which means only one Color shelf, so you cannot add a different field on Color for each of the Measures you are including (they will all get colored by one Field).

So what do we have in our bag of Tableau tricks to get us multiple Marks cards?  Add more measures to the Rows or Columns!  Each measure you add to Rows or Columns creates an axis, and in turn each axis gets its own Marks card.

We will use this to our advantage by creating an axis of '1' for each column we want to have.  You can use the Number Of Records field, or you can create a new Calculated Field and just stick '1' into it:


Use the MIN of this field and add as many of them to the Columns as you need for your view (1 field = 1 measure/column).

Then change the mark types to Text.

Then choose the Marks card label for the first MIN(One) and add your measure to both Text and Color.  Repeat for each measure you want to have a column for.

Now we just need to tidy things up.  You can make the columns skinnier by just adjusting the line that separates the axes on the bottom, and then right-click on an axis (or one of the measures on Rows) and uncheck 'Show Header'.

Now you might be thinking 'What about the Labels..?!' this is where we use a dashboard and some floating text objects to label each column for us.  Drag in a Text object, make it 'floating', and then drag and dop exactly where you want it to go.  Rinse and repeat for each label.


Tip:  use the area in the lower left to adjust the pixel values for  'y' and 'h' to align them all perfectly.
Tip 2:  use a fixed-size dashboard so your labels always stay exactly where you placed them.  If you use Automatic sizing, then the relative placement of your fixed and floating objects will change and you label placement will get messed up.

Booyah.  Cell level formatting in Tableau.

Note:  In this example I use Text for all mark types and I color each column.  There is nothing stopping you from making some columns Shapes or Highlight Boxes (using the Bar mark type) or not coloring some columns at all (I will include an example in the workbook you can download).  This is example just shows the framework which you can extend upon.

Workbook: https://app.box.com/s/cz4zjhnei5hnhtxyepg7e9fj9vxjf3oo

-The Don

Special call out to the O.G. Scott Wasserman for figuring out this genius use of multiple mark cards when tableau introduced Dual-Axis charts way back in the day.  Floating objects in dashboards further simplified the original workaround which required far more steps to get labels for the columns.  Maybe some day we will be able to use Measure Names and Measure Values in calculated fields and this workaround wont be needed anymore, but until then, use it but dont abuse it.



Monday, November 30, 2015

Benchmarking using LOD's (replicating values, freezing values, sub-queries)



A great use case for Level Of Detail expressions in Tableau is to do benchmarking or comparisons.  The scenario is that you have some Dimension column with a bunch of values in, and you want to pick one of the values and 'freeze' or 'replicate' those numbers so that no matter what you filter on, they will still be available to make comparisons.

For instance, say you have a dataset with competitor data in it.  Your company is one of those competitors, but you also have a bunch of market data for the others.  A common thing you might want to do is to compare your company's performance to a specific competitor.  The challenge with this is that if you add a filter to just keep the data for one competitor, then your company's data will be filtered out too, so there is nothing to compare to anymore!  What would fix this is if we were able to 'freeze' our company's numbers so that no matter what we filter on we will always have our company's data available to make comparisons to.

LOD expressions offer an elegant way to achieve this (with no data modeling necessary!). Essentially we can 'fix' or 'freeze' our company's numbers using the FIXED type of LOD expression.  Because of the way Tableau's query pipeline works, any calculations that use FIXED will happen before most other filters are applied.  We can use this to our advantage by fixing our numbers so that when we filter on a single competitor, the math done to compute our company's numbers has already been done and is available for all the other rows of data.  See the below image and notice how Dimension Filters happen after Fixed LOD's are calculated.


Therefore you can filter on any competitor you want and still be able to compare them to your company's numbers.

First let's write a Fixed expression that pre-calculates only the numbers from our company (in the example our company is called 'JM Gebs').  We will find our company's rows with an IF statement, then sum up those rows, and Fix those numbers at the entire-dataset-level:

See!  It works!  Our company's numbers are replicated for all the other companies too!
  
And it sill works even if we filter us out!  Booyah!

Something to keep in mind when using Fixed calculations is that you need to specify any dimensions you want to group those numbers by (for those familiar with SQL, you need to specify the GROUP BY part of the query).  For example, if I want to build a view where I show the comparison at a Country level, then I need to specify in my calculated field to fix/group the math at the Country level:

See now the numbers are different per Country

A consequence of this is that you may need to create many versions of this LOD calc depending on what dimensions you are using in your chart.  You might have a dashboard in which you have a monthly trend of the comparison, a comparison by country, another by segment, etc, so you will need a separate LOD calc for each one of these since they will each need to have different dimensions on the left side of the calculated field (each chart will need the math to be 'grouped by' different dimensions).  Your other option is you can make one calculated field with all the dimensions you want to group by in it (just comma-separate them) - this will work so long as your numbers are additive by nature - i.e. this will not work for something like Unique Customers (which would use CountD, count distinct) - I like just making separate ones for each so I am 100% sure the math is working correctly.


Another consequence of the order of how things are applied - that normal filters (by normal I mean stuff you just add to the filter shelf and dont do anything special to it like make it a Context Filter or make it a Data Source Filter) are applied after the Fixed calculation math happens - is that most other filters you want to add should probably happen before the numbers are 'fixed/frozen', therefore you need to make them Context Filters!  This is not a bad thing at all, but it is just something to be aware of.  I find that I add pretty much every filter besides the one on Company (or whatever you are doing the benchmarking against) as a context filter.

For example, say we have a chart where we are doing the comparison per Country, but we want to let the user filter to a certain Segment (or Year or anything else not being used in the view).  We really want to apply that Segment filter before we pre-calculate our company's fixed sales numbers, because we want to see our sales number just in that Segment.  If we dont make the Segment filter a Context Filter, the math will happen across all Segments and then it will just filter out the rows of data after the fact (which often has no effect, at least not the desired effect).  Again, reference the image above which shows the query pipeline order.


This also applies to Action Filters. For example, if you have a dashboard with two views - a monthly comparison and a comparison by country - and we want to allow users to click on a country and filter the other chart (and vice versa, click a month and filter the countries to just that month) then we would use a Filter Action to do that.  Great! but.... remember that the action filter is going to be applied after the math has already been calculated.  What we really want to happen is if I click on one month, I want to see the country breakdown just for that month.  Therefore in order to make that action filter apply before any of the fixed calculations, we need to add it as a Context Filter.




That should get us the desired behavior.  Now we are able to pick any competitor we want to benchmark ourselves against, and we can use any quickfilters we want to limit by Segment etc, and we are able to use our Filter Actions to drill from one chart to the other on the dashboard, all while being able to keep our number's available for performance comparisons - pretty cool!

-The Don

Workbook:  https://app.box.com/s/pnm61jivsaym5ztrjoeztqlornhjfi3u

*Note - you can set the Competitor quickfilter to multi-select, too.  I just happened to pick single-select (this is one reason why this is better than using parameters to achieve this)

*Note - if you want to show a quickfilter to pick different competitors, but you dont want to include your own company in the list, you can create a Set from the Company field and exclude 'JM Gebs', then show the quickfilter on the Set instead.