5 Replies Latest reply on Jun 4, 2013 10:52 PM by douglas morris

    In one worksheet - Rank on current years Data, and YOY comparison

    douglas morris

      Hi all,

       

      I am attempting to create a dashboard for a colleague

       

      The dashboard needs to

       

      1. Include only the top N of dimension by measure (in the example below I included top 5 suppliers by sales using a set)

      2. Provide performance of that dimension against other measure too - I've included profit in the example

      3. Provide the rank of that dimension against all measures

       

      All of the above needs to be for current FYTD. Hence, date filter added and made contextual for MY's in this FY only

       

      However, the dashboard also needs to show:

       

      4. Year on year (to date) % change for those same measures

       

      It's the fourth thing I can't accomplish, as I've needed to use a date filter to existing FY only to satisfy the other criteria

       

      rank.jpg

      I've attached a packaged workbook.

       

      Can anyone give some tips please?

        • 1. Re: In one worksheet - Rank on current years Data, and YOY comparison
          Robin Kennedy

          Douglas,

           

          One way to accomplish this would be to have a separate sheet for the YoY % difference and then combine the two views onto the dashboard... I know the title of your post says you want it 'in one worksheet' but the end user will not know the difference?

           

          Another solution without the combining on a dashboard workaround would be to duplicate your datasource and create a calculated field in there (to get around the filter on the primary source), or you may be able to use some pass thru (RAWSQL) functions instead.

          • 2. Re: In one worksheet - Rank on current years Data, and YOY comparison
            Jim Wahl

            Hi Douglas,

             

            I started playing with this and came up with another solution. I'm not sure which is easier / better in your particular case, but you might find it helpful.

            4. Year on year (to date) % change for those same measures

            It's the fourth thing I can't accomplish, as I've needed to use a date filter to existing FY only to satisfy the other criteria

            You're right that the filter prevents you from doing the YoY calculation, since the filter is applied at the data source, before Tableau's visual engine and any of your calculations. My approach was to return both years, using a YTD filter.

             

            The fiscal year (I think that's what you mean by FY above?) made this a little tricky. As you probably know, when you make a date field a fiscal date with the format option, you're just changing how Tableau displays the date. Calculations that use that date ignore the FY formatting.

             

            I created a fiscal year function that mimics Tableau's FY display. This is based on Jonathan Drummey's calc that you can read more about here: Year to Date (YTD) from Previous Year and Fiscal Year..

             

            For example, if your fiscal year starts on July, then June 30, 2013 is FY2013 and July 1 is FY2014.

            Order Date Fiscal Year =

            IF MONTH([Fiscal Year Start]) == 1     // In the case of January, FY = current year

            THEN YEAR([Order Date])                  // ELSE FY +=1 if current month > fiscal year start month.

            ELSE YEAR([Order Date]) + IF MONTH([Order Date]) >= MONTH([Fiscal Year Start]) THEN 1 ELSE 0 END

            END

            After adding this, I edited the format (right-click > Default properties > Number format > Number custom) to eliminate the "," separator and add the "FY" prefix.

             

            I duplicated this calc to create Current Fiscal Year, replacing [Order Date] with [Current Date]. After testing, you'd want to replace references to [Current Date] with TODAY().

             

            Then I use this in the Order date YTD Filter. There are additional comments int the attached workbook and again I used a slight variation of Drummey's formula that allows for a January FY start month. For example, if the fiscal month start is October and the current date is Dec 31, 2013, then you want dates between Oct 1, 2013 and Dec 31, 2013 and Oct 1, 2012 and Dec 31, 2012 for the previous FY.

            Order Date YTD for last 2 years FY

            // Order Date > DATE("2012-10-01") AND Order Date < 2013-10-31.

            ([Order Date] >= DATE(STR([Current Fiscal Year]-IIF(MONTH([Fiscal Year Start]) == 1,0,1)) + "-"+STR(MONTH([Fiscal Year Start]))+"-01")

              AND [Order Date] <= [Current Date])

            OR // Previous FY YTD: Order date > DATE("2011-10-01") AND Order Date < 2012-12-31

            ([Order Date] >= DATE(STR([Current Fiscal Year]-IIF(MONTH([Fiscal Year Start]) == 1,1,2)) + "-"+STR(MONTH([Fiscal Year Start]))+"-01")

              AND [Order Date] <= DATEADD('year',-1,[Current Date]))

             

            You also need to create parameters for Current Date---which you could replace with TODAY() after testing---and Fiscal Year Start.

             

            After doing this you can

            • replace your date filter with this field
            • replace Year in the columns shelf with Order Date Fiscal Year
            • add the percent difference table calcs (I used quick table calcs) and get a view similar to "All Fields".

             

            Hiding the previous Year

            You can just right-click on the FY2013 and click hide. But FY2013 will always be hidden, which will cause a problem; for example, if you change the FY start month to January the last two FYs are 2012 and 2013 and 2013 is still hidden. There's a cute workaround to this called a table calc filter, which relies on the fact that filters based on table calcs are applied after all of the data is returned. You create a table calc that duplicates your dimension, in this case Order Date Fiscal Year.

            Order Date Fiscal Year (hide)

            LOOKUP(MIN([Order Date Fiscal Year]), 0)

             

            Then I created a calculated field that's TRUE when Order Date Fiscal Year == Current fiscal year.

            Hide previous FY

            [Order Date Fiscal Year (hide)] == [Current Fiscal Year]

             

            Move Order Date Fiscal Year to the detail shelf, and put Order Date Fiscal Year (hide) to the columns shelf.

            Add Hide previous FY to the filters.

             

            See A Jedi (Filter and Table Calc) Trick | Tableau Software.

             

            If I glossed over a few steps above, let me know. Oh and I didn't thoroughly test this, so  ...

             

            Jim

            • 3. Re: In one worksheet - Rank on current years Data, and YOY comparison
              Robin Kennedy

              Nice solution, Jim!

               

              Had the idea of using the LOOKUP table calc trick at the back of my mind but couldn't quite get it to work without using YEAR in the view... I see that's moot anyway because you can just hide it.

              • 4. Re: In one worksheet - Rank on current years Data, and YOY comparison
                Jim Wahl

                I cleaned up the workbook (attached as _V2 above) and edited the description, after finding the below link in the table calc library (would have saved some time if I had looked there first or @Jonathan Drummey's Wiki ...).

                 

                Tableau Calc Reference Library

                The specified item was not found.

                 

                YTD FY Calcs

                Year to Date (YTD) from Previous Year and Fiscal Year

                 

                I also added a new view to test the YTD FY filter.

                 

                Jim

                • 5. Re: In one worksheet - Rank on current years Data, and YOY comparison
                  douglas morris

                  Jim & Robin - a thousand thanks, you guys are amazing

                   

                  I will test this today. I will also add the wiki and table calc library you speak of to my list of resources. Tried to self serve but could not find anything using google!