- #Legacy connection in excel file in tableau on mac full
- #Legacy connection in excel file in tableau on mac Pc
#Legacy connection in excel file in tableau on mac Pc
In general, Tableau does not allow us to inject rows into our data so I’m going to reach into the data connection and demonstrate a technique that is possible with Custom SQL.įor this example with Superstore Sales, the mention of Custom SQL comes with a caveat, and that is that I’m going to have to use the Legacy Excel connector to take advantage of JET, and as a result I’m afraid this is going to be PC only. If you’re on MAC and have data in Excel you’re going to have to do some pre-processing to either get it into a database or utilise a product like Alteryx. In this case that would be to ensure Tuscany has rows of data for all other Categories, not just the 5 where actual Sales exist. One solution is to pad out our data by the introduction of extra rows where we have a gap. To continue with the Italian example, we have seen how sparse data within one State can impact on our desired output. The aggregation from the Secondary will still operate over all States, but it cannot include Sales from those other Categories whilst Category is an active blending field. Hence when blending, we are only considering from the Secondary those Sales which exist in those 5 Categories. To demonstrate, if we revisit the viz above and select Tuscany we will see there are only sales recorded within 5 of the Categories. The data has been filtered by Country / Region, together with an action filter which is applied on State. The query over the Primary has aggregated our data by Category and Department, but importantly we have active filters, and both filters and aggregation take place before the blend. The reason is perhaps subtle, but fundamental to the blending process and order of operations within Tableau. We’ve taken care to setup the blend correctly, the sum of Sales coming from the Secondary should be agnostic of State.
#Legacy connection in excel file in tableau on mac full
In particular when you select Veneto or Tuscany, you are not seeing the full Italian Market Value. Given what we’ve seen in the previous examples, we might consider this a little odd. Use the viz and observe the results of the filter action: The desire is to see what share of the Italian market is applicable to each State. When you select a State from the map, I am applying a filter action to the crosstab. The crosstab indicates the sum of Sales from the Primary which I am referring to as Market Share, and a sum Sales from the Secondary which I am referring to as Market Value. Importantly I have deselected the default blend on State. I’m again using a self blend, this time I’m blending on Category, Country / Region and Department. I’ve specifically filtered down to a single Country / Region (Italy) with a small subset of active “States” so we have a concise example. I’m delving a little deeper into the Superstore dataset to setup this example. Tip: When blending with dates I like to clear out the automatic relationships and work with custom relationships. Take care to ensure the blend on the date is operating at the Year. I’m then asking Tableau to perform a blend on the Year of this date, along with Category. Here I’m utilising a calculated field “Blend Date” in the Secondary for the purpose of offsetting the Order Date by 1 year.įor consistency I like to have the same named field in the Primary, although it is just defined as a duplicate of Order Date. When they blend together, the higher level aggregation from the Secondary is repeated for each Category. Its a simple example to introduce the power of the self blend, the key step being to ensure you blend on Department, but not on Category which Tableau would enable by default.Įffectively you return a higher level of aggregation from the Secondary (aggregated by Department) than the Primary (aggregated by Department and Category). Here I am calculating the Sales per Category as a percentage of the overall Sales for the Department the respective Category belongs to. Self Blends Example 1 – Sales as a % of Department To set the scene for data padding I’m going work through a couple of blending examples below as an illustration, and for this purpose I’m going to use the Superstore Sales data set as the data source. Laszlo covered this recently in Double dynamite – or duplicating the data connection. The self blend technique involves duplicating an existing data source and invoking a blend between that duplicate and the original. As the title of the blog suggests I’ll then explain how “data padding” can help resolve those issues. I want to take a moment to recap the power of the Tableau “self blend” before drilling into potential issues you might face given the structure of your underlying data. | Mike Lowe Self Blends and Data Padding Introduction