3 Replies Latest reply: Aug 11, 2016 5:51 PM by Andrew Walker RSS

    Comparing Current Year to Date to Last Year Sales Figures

    Cliff Clayman

      I am looking to do a Sales dollars comparison for Current Year to Date to Last Year.  I would like to compare our current month sales figures to that of last years sales for the same month.  I already have a variable set up for vFY (fiscal year), vCY (current year) and vLY (last year) and one for vCM (Current Month).  How can I sum last years sales for the same month as this years current month?  Here is what I have thus far:

       

      Current Year: Sum(If(vFY = vCY, [Sales])

      Last Year:     Sum(If(vFY = vLY and Month = vCM, [Sales])

       

      How can I Sum up all the months up to the current month for last year?  I don't need to do that for current year as I only have the data through the current month.

        • Re: Comparing Current Year to Date to Last Year Sales Figures
          Cliff Clayman

          I can simplify this a little further if it helps.  Can someone just tell me how to get the Sum of Months 1-6 for last year?  I can't even seem to do that.  How can I set a variable that has multiple values or somehow loop through the months that I am looking for?

            • Re: Comparing Current Year to Date to Last Year Sales Figures
              Vishwarath Nagaraju

              May be this ?

              Sum({<DateField = {"$(= '>=' & AddMonths(MonthStart(Today()), -12)  & '<=' & AddMonths(MonthStart(Today()), -6)"}>}Sales)

              • Re: Comparing Current Year to Date to Last Year Sales Figures
                Andrew Walker

                Hi Cliff,

                               I definitely think that the easiest way to do this is by adding a YTD flag toyour calendar

                Something like this:

                 

                Let vStartDate = floor(date('XX/XX/XXXX')); // Put your own start date here

                Let vToday = Floor(Today()); // Of course you can put a future date here if appropriate

                 

                Calendar:

                .

                .

                 

                if(DayNumberOfYear([Temp Date])<=DayNumberOfYear($(vToday)),1,0)  as YTD,

                .

                .

                ;

                Load $(vStartDate) -1 + IterNo() as [Temp Date]

                AutoGenerate 1 While $(vStartDate) -1 + IterNo() <= $(vToday);

                 

                Now you can use the set modifier <YTD = {1}> to pick out values from previous years that you can compare to the current year or simply selecting 1 in a YTD listbox will show previous years' totals to the same day in this year.

                 

                Here's the extract from QV help on the DayNumberOfYear function:

                 

                DayNumberOfYear(date[,firstmonth])

                Returns the day number of the year according to a timestamp with the first millisecond of the first day of the year containing date. The function always uses years based on 366 days.

                By specifying a firstmonth between 1 and 12 (1 if omitted), the beginning of the year may be moved forward to the first day of any month. If you e.g. want to work with a fiscal year starting March 1, specify firstmonth = 3.

                Examples:

                DayNumberOfYear(date) returns the day number counted from the first of the year.

                DayNumberOfYear(date,3) returns the number of the day as counted from the first of March.

                 

                Hope it helps.

                 

                Kind regards

                 

                Andrew