3 Replies Latest reply: Sep 10, 2012 4:11 AM by chuasiuching RSS

    Year To Date Enquiry

      Year MonthAmount
      2011May2
      2011Jun3
      2011Jul4
      2011Aug6
      2011Sep3
      2011Oct8
      2011Nov2
      2011Dec2
      2012Jan8
      2012Feb4
      2012Mar6
      2012Apl4
      2012May2
      2012Jun6

       

      I have data like this and i have no problem that allow user to filter it by clicking the year and month.

      But when i would like to do the year-to-date, i have difficulty for such date range YTD Jun 2012 = 1 July 2012 - 30 June 2012 to be dynamic with this similar formula :

       

      =

      sum({1}if(Month<= $(Month)

       

      I appreciate that anyone can help me and share with me any idea to work it out as such case...

      billion thanks.

       

       

       

       

       

       

        • Re: Year To Date Enquiry
          whiteline _

          There is a function YearToDate.

          • Re: Year To Date Enquiry
            Pradip Sen

            Hi jeffrey_0202

             

            You can use the following formula

            Sum({$<Year={$(=Max(Year))},Month={$(=Max(Month))}>} Amount)

             

            Hope it will help you.

             

            Regards

            Pradip

              • Re: Year To Date Enquiry

                Hi,

                 

                just try to apply your script, it seen doesn't work as it always return me 0 and i have try to replace the max year and max month as follow eg.:

                 

                Sum({$<Year={$(='2012')},Month={$(='Jun')}>} Amount)

                 

                the result i always get is 2012 Jun data only and is 6.

                 

                Enclose to you also for the Script :

                LOAD * INLINE [

                Year11,  Month11, Amount11

                2011, May, 2

                2011, Jun, 3

                2011, Jul, 4

                2011, Aug, 6

                2011, Sep, 3

                2011, Oct, 8

                2011, Nov, 2

                2011, Dec, 2

                2012, Jan, 8

                2012, Feb, 4

                2012, Mar, 6

                2012, Apl, 4

                2012, May, 2

                2012, Jun, 6

                ];

                 

                Appreciate tah you can provide me some advice on this aspecially for dynamic selection : click 2012(year). May(month) it's will should me result from 201107 - 201205. and so on..

                 

                Thanks.