13 Replies Latest reply: Jul 29, 2015 11:01 AM by Uday P RSS

    Sum of Sales YTD

    Uday P

      )Hello Everyone,

      I am trying to perform the sum of sales operation using set analysis in which when only the month and day are selected from two separate list boxes, the sum of sales for current year and the previous years starting date(i.e. 1/1/2015 and 1/1/2014) to the selected day and month are to be summed up and shown. For example if month =July and Day=27, the sum of sales from 1/1/2015 to 27/07/2015 and 1/1/2014 to 27/07/2014 are to be displayed. The Dimensions are Country and year. The expressions tried so far are




      =aggr(sum(Sales),Country, Year)


      =sum({<Date={">=$(vStartDate)<=$(vEndDate)"}Country=, Year=>}Sales)


      =Sum({$<Date= {'>$(=Max((YearStart(Date1)))) <=$(=Max(Date1))'}>} Sales ).



      I have used these same formulae in "if" condition to get current and previous year sales. Also I have used the same formula for Billing Days and it is working fine. Please Help.



      Thanks in advance.

        • Re: Sum of Sales YTD
          Giuseppe Gallina



          can you post sample data?

          Best regards.

          • Re: Sum of Sales YTD
            Jonathan Dienst

            Perhaps these two expressions:


            For selected year:

            Sum(<Date = {">=$(=YearStart(Max(Date))) <=$(=Max(Date))"}>} Sales)


            For previous year:

            Sum(<Date = {">=$(=YearStart(Max(Date), -1)) <=$(=AddYears(Max(Date), -1))"}>} Sales)

            • Re: Sum of Sales YTD
              Giuseppe Gallina

              Only an idea, and using a flag in a mastercalendar?

              For example this:


              let varMinDate = num(peek('DATEFT',0,'Facts'));
              let varMaxDate = num(peek('DATEFT',-1,'Facts'));


              date($(varMinDate) + rowno() -1) as TempDate
              autogenerate $(varMaxDate) - $(varMinDate) +1;

              date(TempDate) AS DATEFT,
              week(TempDate) AS Week,
              Year(TempDate) AS Year,
              Month(TempDate) AS Month,
              Day(TempDate) AS Day,
              inyeartodate (TempDate,Date(Today()), 0) *-1 as CYTDFlag, //FROM START OF YEAR TO TODAY
              inyeartodate (TempDate,Date(Today()), -1) *-1 as LYTDFlag, //FROM START OF PREVIUOS YEAR TO SAME DATE OF PREVIUOS YEAR
              inyear (TempDate,Date(Today()), 0) *-1 as CYFlag,
              inyear (TempDate,Date(Today()), -1) *-1 as LYFlag,
              date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
              'T' & ceil(month(TempDate)/3) as Quarter,
              Week(TempDate)&'-'&Year(TempDate) AS WeekYear,
              weekday(TempDate) AS WeekDay

              ORDER BY
              TempDate Asc;

              DROP TABLE TempCalendar;

              • Re: Sum of Sales YTD
                Johan Lindell

                Hi Uday.


                Here's an example how you can create the formulas in set expression. One thing to think about I usually find is the extra Date around Max (Date). The Max(Date) often returns a number that you need to convert back to a date format for the set expression to work.


                Sum ({<Date = {'>=$(=YearStart (Date(Max(Date))))<=$(=Date(Max(Date)))'}, Month =, Day =>} Sales)



                • Re: Sum of Sales YTD
                  Uday P

                  Found the solution

                  For Current year sales:

                  Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

                  For previous year sales:

                  Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum), -1)))<=$(=AddYear(Max(DateNum), -1))"}>} Sales)


                  Thank you everyone for your time and your help.:)