8 Replies Latest reply on Feb 4, 2019 7:35 PM by Hideki OGAWA

# Creating a common baseline

Hello all,

I am having trouble creating a common baseline.  I've looked out Tableau's Toy Story example and tried to set my data up like that, however, I have no field that gives me number of days.  Attached is an example of my data, I have many more fields, however, I  believe this is all that should be needed for this calculation.  I want a line chart that gives a line for every new product group, that starts at the date active (1) and proceeds, by # of month from there.  Any suggestions on how I can make this work?

I know I am missing something really simple here, but can't wrap my head around it.

Thank you!

Jamie

• ###### 1. Re: Creating a common baseline

Hi Jamie,

I guess this is the same topic as this,

but this time, you already have 'date active' for each product in your data.

Then calculate # of months from active date like this;

DATEDIFF('month',[Date Active],[Invoice Date])

And put this in 'dimension' and use as X axis.

Attached is the example of cummulative sales by this.

I hope this is what you need.

Best regards,

Hideki

1 of 1 people found this helpful
• ###### 2. Re: Creating a common baseline

Hi Jamie

I think the sample workbook only has the first day for each product group so it wasn't possible to see if the solution I propose fully works.

The solution has two parts. One to create a INDEX() based on the Date Active, so that we normalize the first day of any year as index =1.

Then I had to change the SUM(Sales) to another Table Calculation, now using Difference. Why difference? Because each start date has a different value for Sales. So if you want all of them to start at the same point, that point needs to be 0 and then you calculate the difference starting from there.

But if you only do a Difference table calculation you will get a Null for the first point (there is no difference to calculate if you have only one point). So I've added a IFNULL on that Table Calculation and it now shows Index 1 with all dots at the same starting point, 0 (you can click on the color legend to confirm that all dots are there).

I hope when you connect this to more data you will have the lines you expect to see.

I hope this helps (workbook is attached).

Rodrigo

1 of 1 people found this helpful
• ###### 3. Re: Creating a common baseline

Unfortunately, I cannot open your workbook.  I get an error that the workbook is saved in a newer version of Tableau.  Below I took your advise, but am not getting the results.  I think I may have my difference calculation different.  Below is my formula and Table calc for the Week # (don't get stuck on the label, this was copied from toy story.  I will probably rename to Month #)

The Difference calc I ended up with is the below:

I then put  week # on columns and Calc2 on Rows.  I get the below:

I am having trouble figuring out where to go to from here.

@ Mention

• ###### 4. Re: Creating a common baseline

Thank you Hideki,

I can't seem to open the workbook you provided.  It seems I would need a newer version of Tableau to do so.  I did look at the link you provided, and it seems Matt Lupton did exactly what I am trying to do.  I replied to his post as well, to see if I can figure out what I am doing wrong.

Thanks for your help.  I will mark this once I figure this out!

• ###### 5. Re: Creating a common baseline

Hi Jamie,

His data doesn't have 'Date Active' and define the first sales date appeared in the data for each product as day 1.

Your 'Data Active' is not necessarily the first sales date (some product sold months later than Date Active for the first time).

For your case, you should use DATEDIFF, instead of INDEX of sales date (or FIXED LOD should work as well).

Best regards,

Hideki

• ###### 6. Re: Creating a common baseline

Thanks for the clarification Hideki.  I am getting closer.  See attached.  One thing I am noticing is that the datediff calc is not starting everything at 0.  I can't figure out why this didn't work.  Any suggestions?

• ###### 7. Re: Creating a common baseline

Actually both responses helped me.  Hideki helped with the calculation and Rodrigo helped with the calculation to get Sales to plot correctly.  Thank you everyone!  This has been an issue for a couple of months and I finally figured it out!

• ###### 8. Re: Creating a common baseline

The reason why some products start from 'not 0' is because the data is like that.

Taking New Product 3 as an example, month of Date Active is Feb 2016, but first invoice month in this data is Feb 2018.

That is why New Product 3 starts from 24.

If you want to use the first month of invoice date of each product, you should calculate that instead of Data Active,

First invoice date by product (LOD)

{ FIXED [New Product Group]:MIN([Invoice Date])}

then take month difference from each invoice date

Months from First invoice date

DATEDIFF('month',[First invoice date by product (LOD)],[Invoice Date])

Please see 'test' sheet for your review on the attached file (v2018.1) so you can see how these calculation are different.

If you need anything else, please let me know.

Best regards,

Hideki