12 Replies Latest reply: Feb 28, 2012 1:47 AM by jagan mohan rao appala RSS

    Last year figures



      I use an expression to get current year cumulative figures which perfectly works :


      sum ({$<Year = {$(=max(Year))}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"}>} Sales)


      I'd like to display last year same figures. The idea is when i select a month (april 2011 for exemple), I'd like to see sales from january to april 2011 (expression above) and sales from january to april 2010 and I can't figure out how to get this.


      Many thanks for yor help.



        • Last year figures



          You can use following


          sum ({$<Year = {$(=max(Year)-1)}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"}>} Sales)


          This will give you details of previous year.

          Is it ok?

          • Last year figures



            You can probably use the Month field instead of Month-Year to get the result. If you do not already have a month field (Jan,Feb....), you can create the month field by using month(YourDateFieldName) as Month in the script. Then use the expression that Jagan posted.


            As far as  month names are concerned, you could use an inline mapping table for mapping months with their corresponding names as follows....



            LOAD * INLINE [

                Month, M_Name

                1, Jan

                2, Feb

                3, Mar

                4, Apr

                5, May

                6, Jun

                7, Jul

                8, Aug

                9, Sep

                10, Oct

                11, Nov

                12, Dec




            Hope this helps.





              • Last year figures

                Thanks for your answers, but it still doesn't work. The big problem is that I can't do nothing with the script because I work with VPN connection and I can't access to the database.

                Thanks anyway

                  • Re: Last year figures

                    By using partial reload, I could insert a numeric month field. Here is the code :



                    ADD LOAD *,


                    Date(DAT_ADMIN) AS CalendarDate, 

                    Day(DAT_ADMIN) AS CalendarDay, 

                    WeekDay(DAT_ADMIN) AS CalendarDayName, 

                    Week(DAT_ADMIN) AS Week, 

                    Month(DAT_ADMIN) AS CalendarMonth, 

                    'Q' & Ceil(Month(DAT_ADMIN)/3) AS Quarter, 

                    Year(DAT_ADMIN) AS Year, 



                    WeekName(DAT_ADMIN) as CalendarWeekNumberAndYear, 

                    MonthName(DAT_ADMIN) as Month, 

                    QuarterName(DAT_ADMIN) as CalendarQuarterMonthsAndYear,




                    DayStart(DAT_ADMIN) as CalendarDayStart, 

                    WeekStart(DAT_ADMIN) as CalendarWeekStart, 

                    MonthStart(DAT_ADMIN) as CalendarMonthStart, 

                    QuarterStart(DAT_ADMIN) as CalendarQuarterStart, 

                    YearStart(DAT_ADMIN) as CalendarYearStart, 



                    DayEnd(DAT_ADMIN) as CalendarDayEnd, 

                    WeekEnd(DAT_ADMIN) as CalendarWeekEnd, 

                    MonthEnd(DAT_ADMIN) as CalendarMonthEnd, 

                    QuarterEnd(DAT_ADMIN) as CalendarQuarterEnd, 

                    YearEnd(DAT_ADMIN) as CalendarYearEnd, 



                    'Q' & Ceil(Month(DAT_ADMIN)/3) & '/' & Year(DAT_ADMIN) AS CalendarQuarterAndYear,

                    Num(Month(DAT_ADMIN)) AS MonthNo,

                    Year(DAT_ADMIN) & '/' & 'Q' & Ceil(Month(DAT_ADMIN)/3) AS CalendarYearAndQuarter;


                    ADD LOAD date(fieldvalue('DAT_ADMIN',recno())) as DAT_ADMIN

                    AUTOGENERATE fieldvaluecount('DAT_ADMIN')



                    Any idea ?


                    Many thanks in advance.