8 Replies Latest reply on Dec 4, 2019 9:33 AM by Kailash Negi

    Ranking or Sorting dimension based on CAGR calculations

    Kailash Negi

      Hi,

       

      I am looking to achieve the below viz in tableau and I also want to sort subcategories based on different CAGR calculations however not sure if it can be achieved.

       

      Capture.JPG

       

      So instead of this I am trying to just show one CAGR calculation at a time and switch dynamically between different CAGR calculations (i.e. 1 year, 5 years, 10 years) using a parameter and rank them and show top N subcategories. I tried to follow this link https://kb.tableau.com/articles/howto/sorting-by-fields-with-table-calculations

      It works perfectly fine for yearly CAGR calculations but when I try to calculate CAGR at quarter level (i.e. 2 Years CAGR from 2013-Q4 to 2015-Q4) the ranking and top N doesn't seem to be working. Could you please suggest me as to what I am missing here and what sort of changes I will have to do to make it work?

       

      I have attached the workbook for your reference. Sheet3 is where am trying to make changes.

       

      Thanks!

       

      Regards,

      Kailash

        • 1. Re: Ranking or Sorting dimension based on CAGR calculations
          Joe Oppelt

          You have a lot of moving parts here...

           

          Your table calc [ICAGR RANK] has a nested calc:  [!CAGR lookup].

           

          The setting on  [ICAGR RANK] is set to table(down) but the nested setting for  [ICAGR lookup] is set to Table(across).

           

          In this screen shot I'm editing ICAGR RANK, but I have pulled up the setting for ICAGR lookup within that edit screen.

           

          I need to understand more what you are looking to do.  When you are ranking by quarter, what are you looking to rank exactly?

          • 2. Re: Ranking or Sorting dimension based on CAGR calculations
            Kailash Negi

            Hi Joe,

             

            Thanks for looking into this.

             

            What I am trying to do here is rank subcategories based on the CAGR (of 1 year, 2years or 3 years etc.) values at latest quarter so in this case 2015-Q4 and then show only top 5 or 10 subcategories. Since I can't show cagr calculations of 1 year, 2 years, 3 years at the same time on a screen so I am switching between years using a parameter called "N Years" however ranking doesn't seem to be working in this case.

             

            Hope this is clear.

             

            Thanks!

             

            Regards,

            Kailash

            • 3. Re: Ranking or Sorting dimension based on CAGR calculations
              Joe Oppelt

              I didn't follow what you were doing with the !CGAR Lookup calc, so I changed things to rank SUM(Sales) for now.

               

              I changed your Latest Qtr calc to do LAST() = 0.  When done as Table(Across) on a crosstab, it will always display the last column.  (Doesn't matter if you have expanded the hierarchy to Years or Quarters or Months, BTW.  It just shows the last in each (across) row.)

               

              And then I displayed Sum(sales).  And I made a copy of the RANK calc to rank only the last value in each row -- using table DOWN.  So it will take all the LAST values (looking across) and rank those down the sheet.  Play with the date expansion and you'll see the ranking doing what it's supposed to do regardless of the date granularity.  (I think that's the piece you were missing -- making the RANK just look at the last column of data.)

               

              See if you can apply that to your Lookup and !LOD_QTR calcs.  I think you  will need to have the Lookup calc doing Table(across) here because you want it to be looking back one date segment.  The question you will need to address is if you want a quarter lookup to look back 4 quarters (to do the same quarter year over year) or just look back one quarter.  Depending on what you need there, you'll have to run those table calcs differently.

              • 4. Re: Ranking or Sorting dimension based on CAGR calculations
                Kailash Negi

                Hi Joe,

                 

                Thanks a lot for looking into this however it didn't solve my problem.

                 

                You can ignore !CGAR Lookup, I picked that from solution in the link given above to rank my subcategories.

                 

                What I want to do is rank all subcategories based on the latest year and quarter CAGR calculation so in this case CAGR values in 2015-Q4. Next time when I have data for year 2016 then I would like to rank subcategories based on CAGR values at 2016-Q4 and when I have data available till 2017 then I would want to rank subcategories based on 2017-Q4. And I will always show the latest quarter of latest available year data in the viz not other previous quarters or years. Since data is available for 4 years right now so I can calculate 1 year CAGR, 2 year CAGR, 3 Years CAGR based on the input in "N Years" parameter so ranking should change when I enter 1, 2 or 3 in "N Years" parameter input box.

                 

                In your current solution, I still don't see subcategories being ranked based on 2015-Q4.

                 

                Thanks!

                 

                Regards,

                Kailash

                • 5. Re: Ranking or Sorting dimension based on CAGR calculations
                  Joe Oppelt

                  Kailash Negi wrote:

                   

                  ...

                   

                  In your current solution, I still don't see subcategories being ranked based on 2015-Q4.

                   

                   

                  Sheet 3 is doing that.

                   

                  Kailash Negi wrote:

                   

                  ...

                   

                  What I want to do is rank all subcategories based on the latest year and quarter CAGR calculation so in this case CAGR values in 2015-Q4. Next time when I have data for year 2016 then I would like to rank subcategories based on CAGR values at 2016-Q4 and when I have data available till 2017 then I would want to rank subcategories based on 2017-Q4. And I will always show the latest quarter of latest available year data in the viz not other previous quarters or years. ...

                   

                  ...

                  Will you ever have data that only goes up to Q2 of the latest year?  Because that's what my solution will look at.  Not empty Q4 data.  Just the last quarter of the last year in the data.

                  • 6. Re: Ranking or Sorting dimension based on CAGR calculations
                    Kailash Negi

                    Hi Joe,

                     

                    If I'm not mistaken in Sheet3 subcategories are being ranked based on Sales values not CAGR values. Below is the screenshot.

                     

                    Capture1.JPG

                     

                    On your second question, My bad...sorry... yes you are right, I can have data upto Q1, Q2 or Q3 for latest year as well and in that case subcategories should be ranked based on CAGR values of that particular latest quarter values.

                     

                    Regards,

                    Kailash

                    • 7. Re: Ranking or Sorting dimension based on CAGR calculations
                      Joe Oppelt

                      Right.  Way up there I said this:

                       

                      "I didn't follow what you were doing with the !CGAR Lookup calc, so I changed things to rank SUM(Sales) for now."

                       

                      In the attached I made a copy of the rank calc:  [!CAGR RANK (copy) (copy)] and sorted !LOD_QTR instead.

                      1 of 1 people found this helpful
                      • 8. Re: Ranking or Sorting dimension based on CAGR calculations
                        Kailash Negi

                        Thanks a lot Joe for all the help. Really appreciate your prompt responses.

                         

                        Regards,

                        Kailash