#Start0 人がディスカッション中

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

ちょっと気になってみてみましたが、盛大に間違ってましたね。

Lei Chen  が修正してくれたのかと期待したら、なんと遊んでるだけだったので、以下のとおり修正します。

table calc と G.total の組み合わせは、常にややこしいので、正攻法では無理そうです。

なので、以下の準備が必要かと思われます。

接続の関係で、計算式がすべて崩れるので、全部やり直しました。

ちなみに、Number of Date はこうなります。

Thanks,

Shin

Sorry Brittanie,

Are you saying your team is going to stay with HubSpot and expand to using SF as well?

What products from SF do you anticipate you will be getting?

There are a lot of great places to start learning, Trailhead being the best.

For data migration and process migration, there are a lot of things to consider.

The best practice is to map your processes, develop functional requirements, and then to build the system based on those requirements.

Ensure your future users will provide feedback, take this as an opportunity to improve not just carry over existing processes.

I could talk about this for hours, so if you want to connect let me know and you can find me on LinkedIn easily.

Most importantly, make sure you have expert SF advice at least early on for a smooth transition.

Focus on a minimum viable product, not a crazy complex solution, to start with.

Good luck!