4 Replies Latest reply: Oct 3, 2012 3:49 AM by Kamal Naithani RSS

    MTD and YTD

    Renjith Kumaran

      Anybody could please help me to find out the formulas for MTD and YTD, without selecting Year and Month.


      I have done it with selecting Year and Month.



      Thanks in Advance.

        • Re: MTD and YTD
          Dennis Hoogenboom

          Hi Renjith,


          The easiest way I think is to create a calender with a Year and a YearMonth field in your script.


          Try This:


          Load ...


          Year(DATE) AS Year,

          date(monthstart(DATE), 'YYYY-MM') AS YearMonth,


          From ...




          Sum ({$<Year={$(=Max(Year))},   DATE={'<=$(=Today())'} >} SALES)


          Gives you the SUM(SALES) of the max(Year) form your data (up to today)




          Sum ({$<YearMonth={$(=Max(YearMonth))},   DATE={'<=$(=Today())'} >} SALES)


          Gives you the SUM(SALES) of the max(YearMonth) form your data  (up to today)

            • Re: MTD and YTD
              Renjith Kumaran



              the first was working fine.


              but the second (Sum ({$<YearMonth={$(=Max(YearMonth))},   DATE={'<=$(=Today())'} >} SALES))

              returns a null value.


              Please have a look on that.

                • Re: MTD and YTD
                  Kamal Naithani

                  Hi ,

                  It is working wrong because max(yeaMonth) is returning the null value in your case.

                  This is because you must be using....

                  (Month(Date)&'-'& right(Year(Date),2)) as YearMonth for calculating YearMonth or some other method for calculating YearMonth.

                  This is text field and when you are using max(YearMonth) it will return null value.

                  So you have to change it to a number format.

                  So use Like this to make yearmonth

                  The field that you have made Yearmonth,

                  Make a resident table with all the field

                  and use

                  Date(Date(Date#(YearMonth,'MMM-YY'),'DD-MM-YYYY'),'MMM-YY') as YearMonth1,

                  resident your table.

                  To make the YearMonth Number.

                  and use this YearMonth1 in your expression.

                  Hope it helps.






              • Re: MTD and YTD
                Kamal Naithani

                Hi Renjith,

                This can be done in many ways...

                In case you want static result----

                1)Use     inyeartodate(D, $(vToday), 0) * -1 AS CYTD  in back end(vToday=LET vToday = vMaxDate;).

                ansd in expression in FrontEnd use-----
                sum(if(CYTD, LineSalesAmount)).

                For LYTD--
                sum(if(FPYTD, LineSalesAmount))  where FPYTD=inyeartodate(D, $(vToday), -1) * -1 AS FPYTD in Script


                For MTD---sum(if(CMTD, LineSalesAmount))

                Where CMTD=inmonthtodate(D, $(vToday), 0) * -1 AS CMTD in script

                For Previous month this year sum(if(PM, LineSalesAmount))......

                where  PM =inmonth(D, $(vToday), -1) * -1 AS PM  in script


                For Dyanamic result you have to go for the Selection....or you can go as Dennis have recommended but you have to bypass the Year and Month Field.


                =num(sum({<[Fiscal Month]=, [Fiscal Quarter]=,[Fiscal Year]={$(=max([Fiscal Year]))},[ Date]={"<=$(vCurrDate)"}>}[Sales])) .




                Hope it help.I have also bypass Quarter.