Creating a calculated field in Tableau using 3 columns

Question

    Date1         Date2       Line Item    Total

    May10,2009    May9,2009       10        40

    May9,2009     May10,2009      20        10

    May9,2009     May8,2009       20        30

    May8,2009     May11,2009      30        0

This is my Table in Tableau. I want to create a calculated field for last column. If Date2 value has a match in Date1 Column, the new value is equal to the sum of all values corresponding to that that in Line Item column. Is this possible in tableau using calculation or in some way? example: 'May9,2009' in Date2 COLUMN has two occurrences in Date1 Column. hence the two values in Line Item column (20+20) is added and new calculated field shows 40.

Someone please help.

Are the dates in DATE2 distinct or they can be repeated in different rows? — dubafek

dubafek dubafek · Accepted Answer · 2019-04-16T19:55:18

Maybe you can try doing a Join with the same table. Creating new rows matching Date2 with Date1 from a duplicated of the same table. Something like this (Hoja and Hoja12 are the duplicated sheets, my excel is in spanish):

And then calculate de SUM of each row aggregated by Date2. Then you can get a table like this one:

*For [Line Item] itself I had to use average because the data exists in multiple rows due to the join made, but in everyone is the same value. If you want a cleaner way of that maybe you can try using LODs.

Hope this helps.

(Perhaps another approach for the whole problem could be using the LODs, but that means a better comprehension of that kind of expressions and this method was the first one I thought about)

Creating a calculated field in Tableau using 3 columns

1 Answers