6 Replies Latest reply: Jun 9, 2011 5:41 PM by Enrique Chavez RSS

    Problem with dates

      Well first of all, sorry for my english it's out of practice, second, i have a litle problem, i have a date for example 09/06/2011 and i want to sum all the sales between the fitst of january to this date and do that for every year and month that i select, for example

       

      if i have selected june 2011 i have to obtain de sum of january 2011 to  june 2011 and if i select march obtain de sum of janury 2011 to  march 2011 and obtain the same for 2010.

       

      I don´t know of i'm clear but that´s my question and i hope that some one can help me.

       

      thanks.

        • Problem with dates
          Leonard Short

          A set analysis statement like the one below should help you in this situation.

           

           

           

          SUM

          ({$<Fiscal_Year={$(=max(year(Fiscal_Year)))}>}Sales)

           

            • Re: Problem with dates

              correct me if i'm wrong but that only helps me in the current year, what if i wanth a comparation in the same period of months but for the last year

                • Re: Problem with dates
                  Leonard Short

                  You can use something like the following to get previous years:

                  SUM

                   

                   

                   

                  ({$<Fiscal_Year={$(=max(year(Fiscal_Year))-1)}>}Sales)

                   

                  Add month & date selection box's and you can compare as many different time periods as needed.

                  So if you select 2010 as your current Fiscal_Year, the above statement will display data for 2009. If you have no Fiscal_Year selected it will use max - 1, or 2010.

                  Further you can combine them in your expression to just show you the change:

                   

                  SUM

                  ({$<Fiscal_Year={$(=max(year(Fiscal_Year)))}>}Sales)

                  -

                  SUM ({$<Fiscal_Year={$(=max(year(Fiscal_Year))-1)}>}Sales)

                   

                   

                   

                   

                   

                   

                   

                    • Re: Problem with dates

                      thank you, you help me alot to clear that out. one last question if i want to do that but including de days not only the months?, i mean

                       

                      09/06/2011, 09/06/2010

                        • Re: Problem with dates
                          Leonard Short

                          In your load script identify the day of the month somewhere in your tables (1-31) and the name of the month (Jan-Dec) then add those fields into a listbox or multi-box which the users can use to select a specific month or day to compare year over year.

                           

                          Sales:

                          Load SalesDate,

                               year(SalesDate) as SalesYear,

                               quarter(SalesDate) as SalesQtr,

                               month(SalesDate) as SalesMonth,

                               weekday(SalesDate) as SalesDay,

                               day(SalesDate) as SalesDayNum,

                               *

                          From Sales;

                           

                          Putting all of the above fields into listbox's will allow the users to compare virtually any year over year period they wish.

                           

                          Then change the set statement from earlier to read

                          SUM ({$<SalesYear={$(=max(year(SalesYear))-1)}>}Sales)

                           

                          SUM ({$<SalesYear={$(=max(year(SalesYear)))}>}Sales)

                           

                           

                          When a user selects 2009 from the SalesYear listbox, the formulas will look at 2008 & 2009.

                          When they then select 2 from the SalesQtr listbox the formulas will compare Q2 2008 to Q2 2009...

                           

                          Hope that helps!