1 2 Previous Next 17 Replies Latest reply on Jul 29, 2019 12:53 PM by Kelly McGrady Go to original post
      • 15. Re: YoY Calc
        Swarnalatha Mannar Mannan

        Sorry Scott., Its my bad I should have given a little bit explanation.

         

        FY Order Date <= Latest Date AND // this compares  given date to the max date in the dataset

        FY Year = DATEPART("year", Latest Date ) AND // this compares the year of the given date to the year of the max date in the dataset

        (int(((month(FY Order Date))%12)/3)+1) = DATEPART("quarter", Latest Date ) AND // this is for the quarter comparison

        DATETRUNC( "month", FY Order Date) = DATETRUNC("month", Latest Date) // and finally the month

         

        I used the quarter as well since I start my fy year in dec and want the first 3 months (dec-feb) to be the Q1, mar-may as Q2 and so on.

         

        If you need further help, you can post your sample workbook

        • 16. Re: YoY Calc
          Scott Carman

          Kelly,

           

          I ended up using a nested IF statement after looking at all of the options others have offered up here.

           

          So my code looks a bit like this:

           

          IF 2019 Fund THEN

               IF Date = ‘2019 Date’ then

               ELSEIF Date = ‘2019 Date’ THEN

               END

          ELSE IF 2018 Fund THEN

               IF Date = ‘2018 Date’ then

               ELSEIF Date = ‘2018 Date’ THEN

               END

          END

           

          I know my solution is probably not the most eloquent but it gets the job done.

           

          I guess what I find frustrating is the Tableau makes the assumption that everyone's length of time is just 12 months and that all years run from Jan - Dec.  In my case a fund can run for 24 months and the fiscal year runs from Oct - Sep.  Granted, I might be missing some of the understand in the more automated coding samples but the logic suggested seems to fail somewhere along the way and by using a more manual solution, it's consistent and repetitive.

           

          Thanks all y'all for your help and suggestions!

           

          Scott

           

          Ryan Sleeper

          • 17. Re: YoY Calc
            Kelly McGrady

            Hey whatever works! Happy you found a solution

            1 2 Previous Next