6 Replies Latest reply on Feb 15, 2016 3:35 PM by Karan Parekh

    Retrieval of Max Record then aggregating by this value!

    Karan Parekh
    Karan Parekh Feb 9, 2016 2:29 PM

      Hello All!

       

      Background

      So I have struggled with this question for a while, but I believe one of the geniuses on this forum can help me.

      I have a wb that looks at the rate of revenue loss month over month from the previous month's spending population. Pretty straightforward metric. See below.

      Capture.JPG

      - Now the difficult part is adjusting for when a company transitions in ownership from one sales rep to another.

      Currently what happens in my workbook:

      Account transitions from Rep A to Rep B.

      Rep A looks like he loses 100% of this revenue. This should not be the case because Rep A's revenue rate loss is inflated now. See below.

      Capture.JPG

      -in reality, what should be the case is Sales Rep 1254 should not even be in this sheet - all the revenue and the variance should be attributed to rep 1936.

       

      Goal: 1. To be able to find out who owns the company on the last day of the month

                2. This person that owns the account on the last day of the month, inherits all of the revenue, loss, and the rate.

                3. There should only be ONE record for each company.

       

      Basically, group all the data into one row, but for the rep who had the latest ownership of this account. 

       

      Something like this is what I want the end product to look like:

      Capture.JPG

      Notice how much lower the churn rate is now.

       

      Is this possible? I have tried to use the max date to get what i need but to no avail.

       

      Thanks,
      Karan

      • 170 Views
      • Categories: Calculations, Dates & Times, Extracts, Formatting & Display
      • Tags:
        • 1. Re: Retrieval of Max Record then aggregating by this value!
          pooja.gandhi
          pooja.gandhi Feb 10, 2016 7:42 AM (in response to Karan Parekh)

          Hey Karan!

           

          You can create a calculated field like this:

           

          {fixed [Company ID], [Current Team], [Date (Month / Year)] : sum( IF [Date] = { fixed [Company ID],[Current Team], [Date (Month / Year)] : max([Date]) } then [Salesrep ID] end ) }

           

          It it difficult to explain each bit of the calculation, so I suggest dragging all the pills of interest and taking bits and pieces of this nested calc and see how it works, additionally I did drag your date dimension in filter and only selected march and april. I kept rest of your calcs the same and I am assuming a label of 'Loss' in your snapshot above for sales rep 1312 was a typo, because the variance is positive?

           

           

          If you don't understand something, let me know.

           

          Hope this helps!

           

          Pooja.

          1 of 1 people found this helpful
          • 2. Re: Retrieval of Max Record then aggregating by this value!
            Karan Parekh
            Karan Parekh Feb 10, 2016 8:17 AM (in response to pooja.gandhi)

            Hi Pooja,

             

            The Loss above in the Excel snapshot was 100% a typo lol. Thank you very much for pointing this out!!

             

            Thank you so much for taking the time out to view this!! I have a couple of questions/comments:

             

            - I don't think I can use a date field in my filter - unfortunately that is why I set up the calculated fields of April $$ and March $$. Reason being - I'm doing this analysis for the past 15 months.

            Would foregoing the date filter affect the results here? What if I tried to bake that logic into the LOD formula to take into fact that we max the date CASE WHEN date between 03-01 - 03-31?

             

            -Everything you did here makes 100% sense - but why is the grand total showing 0% churn? Ideally, it should be able to sum the LOSS / March Revenue = 11% Churn Rate. Should we be adding something to L.O.D.? Or is my IF statement for labeling messing this up?

             

            Let me know your thoughts Pooja. You have no idea how helpful this is.

             

            Best,
            Karan

            • 3. Re: Retrieval of Max Record then aggregating by this value!
              Karan Parekh
              Karan Parekh Feb 10, 2016 10:17 AM (in response to pooja.gandhi)

              Hey Pooja Gandhi,

               

              Just an additional follow up question - did I miss something here? I have attached my workbook as well. I tried to mess around with the loss formula to readjust the total LOSS (since it should not be this number) but to no avail. let me know if you could work some magic!

              Screen Shot 2016-02-10 at 1.16.53 PM.png@

               

              Best,

              Karan

              • 4. Re: Retrieval of Max Record then aggregating by this value!
                pooja.gandhi
                pooja.gandhi Feb 10, 2016 10:24 AM (in response to Karan Parekh)

                Hey Karan!

                 

                I still have to look at the churn rate question. The formula for Loss revenue would be:

                 

                IF [Variance] < 0 then [Variance] elseif [Variance] > 0 then 0 end

                 

                No LOD required for that one. Also if you do not want to place your date in the filter shelf, you can actually drag the label in filter and exclude 'stagnante' that serves the same purpose. The thing I do not understand is, if you are looking at past 15 months data, are you going to create 15 calculations for each month? That seems to be an overkill, but I do not know the full story so you may be right in what you need to do.

                 

                Hope this helps!

                 

                Pooja.

                1 of 1 people found this helpful
                • 5. Re: Retrieval of Max Record then aggregating by this value!
                  Karan Parekh
                  Karan Parekh Feb 10, 2016 10:49 AM (in response to pooja.gandhi)

                  Pooja Gandhi Ha! Unfortunately yes, the set of calculations for each month. The goal is to see a rolling 15 month rate of revenue loss. We used to use spreadsheets (where literally anything can be done) but now trying to incorporate logic into Tableau.

                   

                  It is arduous, but rewarding at the same time. You see those big spikes in the beginning of the quarter? That is where the companies are shuffled to different owners...hence the reason I was trying to max the date for the right sales rep to inherit the loss.

                  Screen Shot 2016-02-10 at 1.36.22 PM.png

                  I have one more question regarding LOD - if I remove the advertiser or what not - I see that [Current Team] is fixed in the Calculation1. I am trying to work it out on my end so that the -335 is included in the grand total instead of the variance, but I think this is related to the bigger problem of the grand total not adding up.

                  Screen Shot 2016-02-10 at 1.43.10 PM.png

                  Let me know your thoughts? I attached my workbook once again.

                   

                  Best,
                  Karan

                  • 6. Re: Retrieval of Max Record then aggregating by this value!
                    Karan Parekh
                    Karan Parekh Feb 15, 2016 3:35 PM (in response to Karan Parekh)

                    I appreciate your help Pooja Gandhi - I tried to reconcile the grand totals to reflect the true churn % but couldn't find a way. Did you find any success in doing so?

                     

                    Has anyone a solution that can supplement / enhance the ideas laid out here?

                     

                    Best!

                    Karan