4 Replies Latest reply on Apr 2, 2016 6:53 AM by Kenneth Nunez

Help on Calculated Field

HELP!!!

I’m stuck creating a calculated field (Please refer to excel attached). My data has the fields COUNT, DATE, MONTH NAME and I need to "generate a calculated field" that will give as outcome what I "manually" input on "Months After Product Launch" Column (Please refer to Excel Sheet and Vizz attached)

Any suggestions on a calculated field formula to get what I have on “Months After Product Launch”?

My final Vizz shall look like “Forum” Vizz to check the Growth/Maturity/Decrease of several products over time (count of months)

• 1. Re: Help on Calculated Field

Hi Kenneth!

We can use an LOD calc to find the first date for each [Name] with this formula:

{ fixed [Name] : MIN([Date]) }

It looks like there are a few starting rows for each name that have 0 for the value of Count.  Is this how your production data is structured as well or was this just the sample data?

We can then use a DATEDIFF function to compare the current date to the first date per each name to get the number of months between them:

DATEDIFF('month', [Product Launch Date], [Date])

Which will give us a viz like this:

This is slightly different than your sample viz you provided, but that's because the LOD calc is taking into consideration the rows that have 0 for the Count value.

Hope this helps! Thanks

1 of 1 people found this helpful
• 2. Re: Help on Calculated Field

Thanks Dan!! This will help a bit.

Regarding your question, Yes, my production data have 0 for the months that the product is not on the shelf (e,g A and C will be few months after product B). There is any way to move graph A and C to look like the example I gave.

• 3. Re: Help on Calculated Field

Hi Kenneth!

Ok, I think I made some progress, as long as you're alright with the lines not starting from zero and instead starting from their value at "Months after Product Launch" = 1

The first step is to concatenate the Name and Date fields. I used a calculated field to do this but you can also do the same by creating a Combined Field:

[Name] + STR([Date])

Next, I dragged this calc to the Filters shelf and clicked the Condition tab and set it to filter out all the rows where SUM(COUNT) does not equal zero.  After applying the filter you'll notice that the Product Launch Date hasn't changed yet.  Don't worry, we'll fix that in the next step

After clicking OK, go back to the Name+Date field on Filters shelf and right-click > select "Add to context"

Now all the dates will update properly:

The reason this happens is because our Product Launch Date calc uses the FIXED expression for the level of detail calc.  FIXED LODs will be evaluated before normal dimension filters, but AFTER context filters.  So we need to tell Tableau to filter out the Zero lines before we calculate the first date per Name.

After validating the data, we can now go ahead and build out the line chart:

I've also attached a revised copy of the workbook.

Thanks Kenneth!

1 of 1 people found this helpful
• 4. Re: Help on Calculated Field

Thanks a lot Dan!!!!

This is exactly what I was looking for!!!