9 Replies Latest reply on May 26, 2016 7:14 AM by Renato Lyke

    How to create a custom YTD?

    Renato Lyke

      Hi,

       

      My Fiscal year starts from July and ends in June.

       

      If i use the below method to get YTD values . It gives starts from the calendar Year. Instead of the fiscal year.

       

      IF [Date] <= Today() and DATEDIFF('year',[Date],Today())=0

      THEN [Number] END

       

      Is there way that i could get the measure to calculate from July instead of January.

       

      Regards,

      Renato.

        • 1. Re: How to create a custom YTD?
          Mia Lee

          Hi, Renato

           

          You may try creating a DateParameter and set it at July1, 2016.

          Then alter your calc field as below,

           

          IF [Date] <= Today() and [DateParameter]<=[Date]

          THEN [Number] END

           

          Please let me know if this worked.

           

          -Mia-

          • 2. Re: How to create a custom YTD?
            Renato Lyke

            Thanks Mia is there a way i could make this dynamic. Again in the next fiscal i would have to change the date parameter again.

            • 3. Re: How to create a custom YTD?
              Chris Cantrell

              Hello Renato,

              If you right click your date field and go to the default properties, it should let you change the fiscal year starting month. The calculations should be done with the fiscal year start, and it *should* be automatic. At least that worked with some play data I have lying around.

              You'll need to update the FY for each of your date fields however.

              Hopefully that works. If not, I'm up to help out however!

              Happy Tuesday,

              Chris

              • 4. Re: How to create a custom YTD?
                Renato Lyke

                Thanks Chris. I tried the same and it did not work.

                • 5. Re: How to create a custom YTD?
                  Chris Cantrell

                  Hey Rentao,

                  How about this?

                  Ok, so, we need to establish, the FY start, July or any given year.

                  Maybe we can build it...

                  date.JPG

                  So..

                  So putting the date between... Future date... and then Past date...

                  The equals... heads to...

                  IF [DateofEvent]<DATE(STR(DATEPART('year',TODAY()))+'-'+'7'+'-'+'1'+' '+'00:00')

                       AND [DateofEvent]>=DATE(STR(DATEPART('year',TODAY())-1)+'-'+'7'+'-'+'1'+' '+'00:00')

                       THEN [Number] END

                   

                  At least my Tableau thinks this is a valid function... It does appear to work with my play data, give or take some minor tweaking.

                  Step forward? I think?

                  Arg.

                  The "today" function will tweak it after the first of the FY.

                   

                  EDIT: Removed an incorrect equal sign.

                  Chris

                  • 6. Re: How to create a custom YTD?
                    Chris Cantrell

                    Hey Renato,

                    Mia's solution is actually much better I think, much clearer and easier to follow:

                    IF [Date] <= Today() and

                    (

                    year([date])=year(today())

                    and month([date])>=7)

                    or

                    year([date])+1=year(today())

                    and month([date])<7)

                    )

                    THEN [Number] END

                     

                    I guess we have a rather important question. Is this strictly an end of the year report? Would we need to be concerned with it being re-evaluated after the first of the next year?

                    If it is an end of the year report, I'd change it to be:

                     

                    IF [Date] <= Today() and

                    (

                    year([date])=year(today())

                    and month([date])<7)

                    or

                    year([date])-1=year(today())

                    and month([date])>=7)

                    )

                    THEN [Number] END

                     

                    If further re-evaluation or comparison might be made in the future, you might have a few additional changes to make. Mia's solution is cleaner though, so I'd go with that direction.

                    • 7. Re: How to create a custom YTD?
                      Mia Lee

                      Hi, Renato

                       

                      This should work

                       

                      IF

                      (
                      [Date]<=today() AND MONTH(today())>=7 AND YEAR(today())=YEAR([Date])
                      AND MONTH([Date])>=7
                      )
                      OR
                      (
                      [Date]<=today() AND MONTH(today())<7 AND
                      (
                      YEAR(today())=YEAR([Date]) AND MONTH([Date])<7
                      OR
                      YEAR(today())-1=YEAR([Date]) AND MONTH([Date])>=7
                      )
                      )

                      THEN [Number] END

                       

                      I have altered someone else's sample workbook to demonstrate your case as attached. You can change the "Choose Start" parameter to test the calculated field above.

                       

                      Hope this helps. Let me know how it worked.

                       

                      -Mia-

                      • 8. Re: How to create a custom YTD?
                        Renato Lyke

                        Sorry guys. Was out. It would be a comparison report. When i would check YTD and previous YTD. In the same report i would use MTD and QTD as well.

                         

                        Will check and get back to you.

                        • 9. Re: How to create a custom YTD?
                          Renato Lyke

                          Thanks Mia. It works great.