4 Replies Latest reply on Feb 28, 2017 2:52 PM by Bjorn Johnson

    Calculation of 3 measures on 3 different sheets with 3 different date names

    Bjorn Johnson

      So I have an Excel data source with three different sheets, all tied together on an ID field. I'm working with funnel data, in that the first sheet contains Lead information, the second sheet contains Opportunities, and the third sheet Conversions. I've been counting each measure by COUNT([Id]) for Leads, COUNT([id](Opportunities)) and COUNT([id](Conversions)) Each of these measures have different date values: [Lead Date] for leads, [Opportunity Date] for opportunities, and [Conversion Date] for conversions. An example calculation I want to make is a YOY difference of each of these measures but in one table, and I cannot use [Lead Date] for opportunities and conversions, since opportunities and conversions occur some amount of days AFTER [Lead Date].

       

      I want a table with Year nested within Week Number (Week Start=Monday) for the rows with the measure names as the columns so I can do a YOY Difference calculation. But I need to use [Lead Date] for Leads, [Opportunity Date] for Opportunities, and [Conversion Date] for Conversions. So for example the table's row with Week 2 2017 would be 01/02/2017 for [Lead Date], 01/02/2017 for [Opp Date], and 01/02/2017 for [Conversion Date] until 01/08/2017.  I can easily do this with three different tables in three different work sheets, but I want it all in one table. I would also like to add YOY growth (which should be easy once the YOY difference is able to be done) and I also want to create calculations for ratios of these measures: Opportunities/Leads, Conversions/Opportunities, and Conversions/Leads.

       

      I think I need to create a calculation field, for instance called Cohort Week, where it would attribute [Lead Date] for Leads, [Opportunity Date] for Opps, and [Conversion Date] for conversions in the calculations. But then I also need a way to show the year: so it would be week 2: and then two rows for 2016 and 2017. I'm not sure if I'm overthinking this or what, but I'd really appreciate some help on this.

        • 1. Re: Calculation of 3 measures on 3 different sheets with 3 different date names
          Joshua Milligan

          Bjorn,

           

          It's really hard to get specific without seeing your data and I'm not quite certain what you mean by "all tied together on an ID field" -- I'm assuming you are joining.  In general, this issue sounds like a case where a UNION might be a better option than a join.  This would allow you have a single date field (merge the fields if they have different names in the sheets) and a single measure (you could still use COUNTD([ID] if needed), but you would have the Sheet and Table Name fields that would identify whether a record is a Lead, Opportunity, etc... and that becomes a dimension that you can use to slice the data (rather than having to have multiple measures to do it).

           

          Ultimately, this shape of data would be much easier to work with (for most things) and would allow you to do a YoY comparison in one view without crazy calculations.

           

          Hope that helps!

          Joshua

          1 of 1 people found this helpful
          • 2. Re: Calculation of 3 measures on 3 different sheets with 3 different date names
            Bjorn Johnson

            Thanks for you reply! I attached an example packaged workbook.

             

            I do need to join ID's together, because there are some fields in the Leads sheet (not in the other sheets) that I need to filter Conversions and Opportunities with and creating a Union doesn't attribute this filter to the right IDs in the Conversions sheet.

             

            I need the view from the One Table sheet in the packaged workbook, but as  you can see from the next worksheet that has Opportunity Date, the numbers are different for each week number, because there are multiple Opportunity Date weeks for each Lead Date week, since many opportunities occur days or weeks after the original lead (same with conversions).

             

            I'm not quite sure if the One Table view is able to be recreated; I can only create three separate worksheets and then export those crosstabs to Excel where I can recreate the One Table view, but I would really like to be able to get this more automated in Tableau as one view.

             

            I really appreciate your help!

            • 3. Re: Calculation of 3 measures on 3 different sheets with 3 different date names
              Joshua Milligan

              Bjorn,

               

              Thanks for attaching the workbook -- it's highly likely that I won't get to it today or even until next week.  But in the meantime, you said

               

               

              I do need to join ID's together, because there are some fields in the Leads sheet (not in the other sheets) that I need to filter Conversions and Opportunities with and creating a Union doesn't attribute this filter to the right IDs in the Conversions sheet.

               

               

              Please don't discount the union too soon!  You can use level of detail expressions to find the attributes you want and then bring it back to a row level for filtering (or anything else).

               

              For example, let's say you really need Category which only applies to Leads. With the union, Category is null for everything except for Leads.  But you can create a calculation like:

               

              [ID Category]

              {FIXED [ID] : MAX(Category)}

               

              Which will find the Max Category for an ID (which will be the value for Leads since it is null for everything else and NULLs are not picked up by MAX()).  You can make this new calculation a dimension and you now have the value you want for every ID (even if it is not a Lead).

               

              And, if your Category can change over time, then you might need to adjust the calculation like this:

              [ID Category]

              {FIXED [ID],[Date] : MAX(Category)}

               

              which will give you the Category for every ID for every date.  Granted, it might be even more complex than that - but still should be doable.

               

               

              But the point is: I think the union is still the structure that will enable you to meet your initial goal the most effectively.

               

              Hope that helps!

              Joshua

              1 of 1 people found this helpful
              • 4. Re: Calculation of 3 measures on 3 different sheets with 3 different date names
                Bjorn Johnson

                Thank you so much for taking to time to help me with this!

                 

                The packaged workbook example I posted is only using this year's data, we have the same report and format for last year's data so I just append that file to the extract to get 2016 data.

                 

                I think you're definitely right in using the Union to get my dates attributed how I want them to be. Thanks for explaining how to get Category to be attributed to all of the sheets in the union. I did that; however, I am still seeing different numbers in the Union workbook compared to a Join workbook with three separate sheets for the three separate dates. So when I use a Join to create three separate tables, the numbers are right, but I would rather have one table so that filtering and such is easier. In the Union workbook, the first sheet's data are right when I filter by certain dimensions (e.g. Category from: {FIXED [ID] : MAX([CATEGORY])}), but the other two sheets' data is not right.