#End0 diskutieren mit

Hi @Venkat, thanks for posting the links (FYI I'm a co-author of the Covariance, Trend Lines, Correlation Coefficient R and R-Squared document), unfortunately none of the links actually answer Sumeet's question. Next time please make sure to test the instructions you are linking to against the data and/or workbook that the questioner has provided to make sure that the link is actually useful for the problem at hand.

Hi @Sumeet,

You didn't say if you needed the trend line results as a display or to use in a subsequent calculation, so there are a couple of options here. Also you weren't clear if you wanted to use Tableau's native trend lines or the result from Excel, and that creates different options as well.

I hadn't studied this before, it turns out that x for Excel's linear trend line and Tableau's linear trend line are computed in different ways so the m (slope) and b (intercept) of y=mx+b are different in each tool.

Tableau's Trend Lines

In Tableau x is the value of each mark of the pill on Columns. In the case of dates then x is the date converted to a number, which for an Excel source is the number of days since 0/0/1900 (with an additional day for the non-existent leap year in 1900 due to some very old design decisions).

Therefore in this view where I've converted the original string date field into a field of a date data type (using a custom split on the string, changing the data type to date, and then using a custom date format to match the original date format) the trend line is -995.78Date - Split 1 + 4.57618E+07 where Date - Split 1 is x.

Excel's Trend Lines

In Excel x is the index of whatever series is creating the X axis so the first point is 1, the second point is 2, and so on. In the screenshot below are three trendlines that all have the same -6970.5x + 3E+06 formula. The first is the original line using the string dates, the second is the dates converted to numbers, and the third uses a simple index from 1-13:

Therefore Excel and Tableau have different default behaviors: Excel uses an index for the x-axis, Tableau uses the value on the x-axis. Both are effectively accurate, and you can pick the one you want to use. However to get a view that exactly replicates Excel's results in Tableau takes some extra effort.

To start, here's a view with the Excel trend line results using Tableau's native trend line where instead of the Date on Columns I'm using the INDEX() table calculation with compute using on the Date:

There's no way to get that Index axis to have the original date structure of the string Date field that I can find. Therefore to get the  Excel results in Tableau *and* have the original date structure on the x-axis requires custom calculations. I used the WINDOW_COVAR formulas that require Tableau v10.2 or higher described in Covariance, Trend Lines, Correlation Coefficient R and R-Squared where x = INDEX() and y = SUM($).

Here's the workout view...the table calculations all have a compute using on the Date dimension:

Now here's a view where the workout view was duplicated and the y=mx+b equation placed in the title:

In this view I used a dual axis to create a line for the trend on the secondary axis using marks:

Tableau doesn't presently support dashed marks for lines, there are a couple of different tricks if you want that.

Here's one: in this view I made the trend line marks have 0% opacity and then added a trend line on top of that to get the dashed lines. However this view has problem in that when hovering over the trend line the trend line tooltip shows an R squared of 1 and a p-value of < 0.0001 and these are inaccurate because they are a trend line based on the trend line marks and not the actual data. Note that in this case I also had to switch to use using the Date - Split 1 dimension (and change the compute using of the table calculations) because Tableau's trend lines require a continuous date or number.

v10.2 workbook is attached, let me know if you have any questions!

Jonathan

Hi Stephen

The key steps are:

1. Use an Excel file as a data source when creating your Tableau file
2. When finished, export your Tableau file as .twbx
3. Manually rename the .twbx it to a .zip.
4. Open the zip and delete the cache (if one exists)

That zip then becomes the template to create other twbx files, each with a different data set.

The zip contains an Excel file, and it’s this file you swap in and out with VBA or Powershell to generate new twbx files.

The location of the Excel file in the zip is

Excel set up:

Have a single Excel Template Workbook with a table containing a data connection to SQL. (if you need multiple data sources, add each on as extra sheets as below)

Have a single Excel Control Workbook which contains a VBA loop to:

• Open template
• Update SQL connection details for next customer each customer 
  Example: Change [Exec MySQLProcedure @Customer = X]  --> [Exec MySQLProcedure @Customer = Y]
• Refresh the SQL connection to bring back new data
• Break the SQL connection
• Save the into it’s own directory: \MyCustomerName\Data\Documents\MyExcel.xlsx
• Repeat for all merchants

The name of the Excel file should be identical in every case. However it should be saved into a new MyCustomerName directory.

(I can send you some code for this Excel process if you like, however I'll leave it off for now to keep the post size down)

VBA for replacing files inside a zip:

After the template has created multiple .xslx files, you swap them into the .zip file.

The code I used for this is below:

'---------------------Sets up Winzip shell extension for VBA-----------------------

'---------------------Place this at the top of your code module--------------------

#If VBA7 Then

    Private Declare PtrSafe Function OpenProcess Lib "kernel32" _

        (ByVal dwDesiredAccess As Long, _

        ByVal bInheritHandle As Long, _

        ByVal dwProcessId As Long) As Long

    Private Declare PtrSafe Function GetExitCodeProcess Lib "kernel32" _

        (ByVal hProcess As Long, _

        lpExitCode As Long) As Long

#Else

    Private Declare Function OpenProcess Lib "kernel32" _

        (ByVal dwDesiredAccess As Long, _

        ByVal bInheritHandle As Long, _

        ByVal dwProcessId As Long) As Long

    Private Declare Function GetExitCodeProcess Lib "kernel32" _

        (ByVal hProcess As Long, _

        lpExitCode As Long) As Long

#End If

Public Const PROCESS_QUERY_INFORMATION = &H400

Public Const STILL_ACTIVE = &H103

'----------------------------------------------------------------------------------

Public Sub ShellAndWait(ByVal PathName As String, Optional WindowState)

    Dim hProg As Long, hProcess As Long, ExitCode As Long

    'fill in the missing parameter and execute the program

    If IsMissing(WindowState) Then WindowState = 1

    hProg = Shell(PathName, WindowState)

    'hProg is a "process ID under Win64. To get the process handle:

    hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)

    Do

        'populate Exitcode variable

        GetExitCodeProcess hProcess, ExitCode

        DoEvents

    Loop While ExitCode = STILL_ACTIVE

End Sub

'----------------------------Code for creating zip files--------------------------------------

'1.Copies Template zip and renames as merchant  2.Adds merchant data folder to zip

Sub CreateTableauWorkbooks

    Dim sZipProgramPath As String

    Dim sZipTemplatePath As String, sFolderToBeZipped As String, sZipNewFilePath As String

    Dim sShellStr As String

    Dim rZipName As Range

sZipProgramPath = “C:\program files\winzip\”

    sZipFolderPath =  ThisWorkbook.Path & "\Tableau\"   ‘This is where they get saved

    For Each rZipName In Range("MerchantTable[Customer_Name]")    ‘loop a list of merchants

        sFolderToBeZipped = sZipFolderPath & rZipName.Value & "\"  'Zips all files inside this folder

        sZipNewFilePath = sZipFolderPath & rZipName.Value & ".zip" 'New name of zip created

        sZipTemplatePath = sZipFolderPath & Range("TemplateZipName").Value

        FileCopy sZipTemplatePath, sZipNewFilePath                  'Create Zip File ready for new data

        If Dir(sZipProgramPath & "winzip64.exe") = "" Then Exit Sub  'Check if this is the path where WinZip is installed.

        On Error Resume Next

        sShellStr = sZipProgramPath & "Winzip64.exe -min -a -r" & " " & Chr(34) & sZipNewFilePath & Chr(34) & " " & Chr(34) & sFolderToBeZipped & "*.*" & Chr(34)

        Call ShellAndWait(sShellStr, vbHide)

        Name sZipNewFilePath As sZipFolderPath & rZipName.Value & ".twbx"

    Next

End Sub

Robin