7 Replies Latest reply on Aug 16, 2016 5:38 PM by Elavar sevvel

    Why do we need to create a calculated field to blend the data ?

    Elavar sevvel

      Show Records That Fall Within a Period of Time | Tableau Software

       

      1.In One of the beautifully explained article,I would like to understand te step 2.

      This step uses calculated fields to blend data. You create a new date dimension to represent the order date and the ship date. Then Tableau can blend the two dates so that you can calculate the difference between them.

      I don't understand why the 'Date' calculation field needs to be created?

      'Date' has only '[Order Date]' as its definition in it.

       

      2.Build the view for the difference between ordered and shipped products

       

      If I build the view against the 'order date' and Number of Active Products, I am getting values as 0. I don't understand why I get it as 0.(Please refer to sheet 4 in the attachment)

      But if I build the view against the 'Date' and Number of Active Products, I am getting the correct value.

       

      Anybody who helps me to understand my doubt, I will be thankful to you all.

        • 1. Re: Why do we need to create a calculated field in the blending of data ?
          Phillip Overpeck

          It is because you are blending on two different date fields. One data source is using Ordered date and the other is using Shipped date. Because both data sources have both of those date fields, tableau is automatically blending them on their counterparts.

           

          The easiest and cleanest way to overcome this it to create the Date field in each data source, but supply them with the date you want to blend on. In this case you use Shipped data in the date field from one data source, and Ordered date in the date field on the other source. Tableau automatically creates the relationship for you because the field has the same name, but the dates are actually different.

           

          You could make it work without using the Date field you created, but you would have to manually edit the relationships between the original date fields. Which could cause you issues when trying to blend for other purposes.

           

          I hope this helps

          • 2. Re: Why do we need to create a calculated field to blend the data ?
            Elavar sevvel

            Thanks Philip. Can you please help me to understand the below point

             

            2.Build the view for the difference between ordered and shipped products

             

            If I build the view against the 'order date' and Number of Active Products, I am getting values as 0. I don't understand why I am getting it as 0.(Please refer to sheet 4 in the attachment)

            But if I build the view against the 'Date' and Number of Active Products, I am getting the correct value.

            • 3. Re: Why do we need to create a calculated field to blend the data ?
              Phillip Overpeck

              If you look at the definitions of the [Ordered].[Date] field and the [Shipped].[Date] field, you will notice that they aren't the same.

               

              [Ordered].[Date] = [Order Date] and [Shipped].[Date] = [Ship Date].

               

              When you blend on that field you are essentially blending [Ordered].[Order Date] to [Shipped].[Ship Date]. So you aren't blending on the same date fields. When you just blend on [Order Date] you are blending on [Order Date] from both sources.

               

              The [Delta] calculation you are using is reliant on the blend between  [Ordered].[Order Date] and [Shipped].[Ship Date] to work. So when you just blend on [Order Date] from both sources it will always result in a 0.

               

              That calculation is counting the number of Orders in the [Ordered] data source and subtracting the number of Orders in the [Shipped] data source. When just blending on [Order Date] you are counting the same rows. But when you blend on [Ordered].[Order Date] and [Shipped].[Ship Date] you are counting the data under different conditions, yielding potentially different results, which in turn yield the correct delta.

              • 4. Re: Why do we need to create a calculated field to blend the data ?
                Elavar sevvel

                Thank you so much for your time to explain the things.

                • 5. Re: Why do we need to create a calculated field to blend the data ?
                  Elavar sevvel

                  Sir, If you don't mind I have a question as  in this statement 'The [Delta] calculation you are using is reliant on the blend between  [Ordered].[Order Date] and [Shipped].[Ship Date] to work. So when you just blend on [Order Date] from both sources it will always result in a 0.' 

                  The blend happens at [Ordered].[Order Date] and [Shipped].[Ship Date] and not on the Delta Vs Order date, Am I correct ?. This is what confusing me. I want to know what is the query executed in tableau to build this viz.

                  Thank you  Sir!!

                  1 of 1 people found this helpful
                  • 6. Re: Why do we need to create a calculated field to blend the data ?
                    Phillip Overpeck

                    I am not following your question.

                     

                    The Delta is a calculation based on the Distinct Count of [Order Id]. You are Subtracting the Distinct Count of [Order Id] on the Ship Date from the Distinct Count of [Order Id] on the Order Date. It doesn't have it's own date fields.