4 Replies Latest reply on Feb 9, 2012 8:55 AM by lynn.walton

    How to show the Diff of a % Diff on one level of AGG and a %Diff on another level of AGG?

      Hello,

       

      I'm new to tableau and have been studying very hard for a few weeks now, including many examples in these forums as well as all the "intro" and "advanced" tutorials.

       

      I need to be able to show "Specialists" the accounts (which in the attached sheet is a Web Id/Locale) that they manage in the following way:

       

      •   Need to do something which lets them quickly see which accounts have the greatest negative in % Diff in Sum(Visits) from the most recent month compared to the same month the previous year.   But, also need to be able to show how that %Diff for the individual Web Id/Locale compares with the %Diff for Region in which the WebId/Locale resides (called DMA in the sample).   Of course the %Diff for the Region would also be the same period, most recent month compared to same month previous year.
      •   Need to do the same thing to let them quickly see which accounts have the greatest negative in % Diff in Sum(Visits) from the most recent month compared to the previous month.  And again, also need to be able to show how that %Diff for the individual Web Id/Locale compares with the %Diff for Region in which the WebId/Locale resides (called DMA in the sample).   This time the %Diff for the Region would also be the same period, most recent month compared to previous month.

       

      What I'd really like is a way to Diff the two %Diffs for all their accounts and order it by the ones where the performance

      of the account is the worst compared to the performance of the Region (DMA).

       

      So the attached workbook shows on Dashboard 1 shows my first steps in getting to understand how to even get the %Diff calculations I want for each case.  However, I really would like the comparisons I described above in the same sheet/view. 

       

      Right now to see how they compare, they'd have to highlight the problem account on the left to have it filter the DMA to just show what that DMA's % Diff for the same period is, and concentrate on the two numbers to compute the diff in their mind.   Can anyone give me ideas on how to accomplish something like what I'm asking?

       

      Much thanks in advance,

      Lynn

        • 1. Re: How to show the Diff of a % Diff on one level of AGG and a %Diff on another level of AGG?
          Andy Cotgreave

          Hi Lynn

          FIrst of all let me say that what you've worked out so far is great stuff considering you're new to Tableau. I've had a go at working out what you're trying to do, but I think you're questions are too vague. That's not because you haven't taken the time to explain them properly. It's because the data's complex and your requirements are complex to explain. Can you try reposting with some other way of explaining what it is you want.

           

          Maybe with screenshots and a clearer example of exactly which fields you need in your calculations?

           

          Andy

          PS - I'll be honest - I spent 15 minutes on this and so far got as far as recreating your first sheet; I needed to do that in order to decipher the starting point. I fear that the full solution you need might be more time than I have available!

          • 2. Re: How to show the Diff of a % Diff on one level of AGG and a %Diff on another level of AGG?

            Thanks for your reply Andy.  I appreciate your help and hope the following screenshots will clarify what I'm trying to do.

            excel_column_explaination.jpg

            So what the above is saying is that if the prev vs curr mo visits for test-dealer1 were 2500 and 3000 respectively, then I had a 20% improvement for the month.  But the visits for prev vs curr mo for the Los Angeles DMA improved 10%.  So the test-dealer1 webId outperformed the DMA by 10% - the number in the last column. 

             

            Likewise, the test-dealer2 had a 14.89% decrease while the DMA improved 10% so test-dealer2 under-performed the DMA by 24.89%.  

             

            Ultimately, I probably want the view to only show the 1st, 2nd and last columns like this and be sorted by the 3rd column so the most red is at the top.  Though I like a view like above while I'm working so I can verify my numbers more easily. 

             

            We're just trying to get at some kind of visual display that quickly lets someone who has little time look at a list of around 200 and identify the ones that performed the worst very easily.    (Where "performed the worst" in this case means when computed like the above screen shot does to arrive at the last column.)

             

            All the other data in the sample workbook I posted is just there to help when providing other types of views for drilling down, etc.

             

            sample_viz.jpg

            Note: this last screenshot is take from a view where I brought in the %Diff from prev mo to curr mo for both the webIds and DMA's already pre-calculated from the database query.  And I'm still struggling to get it to sort like I want.  I don't know if it is a good idea to bring in the data "pre-calculated" in this case or not. It makes what I'm trying to do easier, but causes more extracts that have to be refreshed, and limits my other data for drilling down, unless I still bring in the "raw" data as additional data sources.  I was told it's better to let Tableau do the calculations but I'm still trying to learn if that's always true. Usually there are tradeoffs and I don't know enough tableau yet to determine them.

             

            One final note:  I do need this to be filterable by the specialists like I have in my original sample.

             

            Thanks again for your help Andy and to anyone else who might contribute too!

             

            Lynn

            • 3. Re: How to show the Diff of a % Diff on one level of AGG and a %Diff on another level of AGG?
              Andy Cotgreave

              Ok, i get it now. One thing to confirm Prev/Curr Month is this month and the same month last year? Or is it literally this month (say, Feb) and last month (Jan)?

              • 4. Re: How to show the Diff of a % Diff on one level of AGG and a %Diff on another level of AGG?

                Well, in my original sample workbook I posted, I had both because my requirement is to be able to quickly show user how all their webId's compared to the DMA level for both: 1) the most recent month vs previous month (MoM) (Feb 2012 to Jan 2012) and 2) the most recent month to same month of previous year (Feb 2012 to Feb 2011).  

                 

                I will still need to do both but I was trying to simplify the explanation.  I'd probably love to learn if both can be displayed in the same view but am prepared to have the MoM one and the Mo over Same Mo Prev Year one be in separate sheets shown in one dashboard if needed. So my screenshots are assuming we're just going for one in a sheet and using the MoM one as the example.

                 

                The data I get from the datasource will have all the data for the 15 months PRIOR to the current month.  The actual current month relative to today will never be available. So the "most recent month" in my data is what we do the calculations based on but it's not actually the "current month" relative to today. 

                 

                Thanks again!