8 Replies Latest reply: Oct 11, 2012 7:50 AM by Kamal Naithani RSS

    Dates in Set Analysis

      Hi Everyone,

       

      2 parts to this post, Part 1:

       

      I have a fiedl, [1L Date], which is a date field. I am using the following Set Analysis expression

       

      =SUM({<[1L Date]={"<=DATE(AddYears(today(),-1))"}>} quantity)

       

      I am trying to get the sum of all quntities in the last 365 days

       

      Part 2

       

      Same as Part 1, except I want the Sum of all Quantities from the previous 365 days so something like

       

      =SUM({<[1L Date]={">=DATE(AddYears(today(),-2))", [1L Date]={"<=DATE(AddYears(today(),-1))"}>} quantity)

       

       

       

      Both expressions are returning 0 at the minute, please can someone help,

       

      Kind Regards,

       

      Miles

        • Re: Dates in Set Analysis
          Jonathan Dienst

          Miles

           

          Try these

           

          =Sum({<[1L Date]={"<=$(=DATE(AddYears(today(),-1)))"}>} quantity)

           

          =Sum({<[1L Date]={">=$(=DATE(AddYears(today(),-2))) <=$(=DATE(AddYears(today(),-1)))"}>} quantity)

           

          Hope that helps

          Jonathan

            • Re: Dates in Set Analysis

              Hi Jonathan,

               

              Thanks for your reply, unfortunately this still comes up with a 0, any more ideas?

               

              Kind Regards,

               

              Miles

                • Re: Dates in Set Analysis
                  Miguel Angel Baeyens de Arce

                  Hello Miles,

                   

                  Does those fields have an actual (numeric) date field so the value returned by the function Date() is exactly the same as the stored in the field? Is there any chance that there might be some differences because of the regional settings in Windows?

                   

                  Miguel

                  • Re: Dates in Set Analysis
                    Jonathan Dienst

                    Miles

                     

                    Where are you trying to use these expressions - in a text box or in a dimensioned chart - the above will work in the former, but possibly not in the latter depending on your data model and the chart dimensions?

                     

                    And are you selecting any date related fields? That field selection may be limiting the data returned.

                     

                    Have you tested the expression with literal dates (work out the date corresponding to the calculation, eg

                     

                         =Sum({<[1L Date]={"<=2011/10/11"}>} quantity)

                     

                    Finally, do you actually have data in the date ranges being selected?

                     

                     

                    Regards

                    Jonathan

                      • Re: Dates in Set Analysis

                        Hi guys,

                         

                        Thank you for your support. I have been playing around with trying to get these values to work in a textbox first before jumping ahead and putting it into a table, but I got this formula to work,

                         

                        =sum({1<[1L Date]={">=$(=addyears(max([1L Date]=))-3) <=$(=min([1L Date]))"}>}quantity)

                         

                        BUT I don't know why this works and Jonathan's suggestion didn't, please can someone explain so I can learn?

                         

                        P.S. in terms of regional settings, it is a possibility that something like this might be happening as I am dragging data over from a US server...it could be an explanation, in which case how would you recommend I combat that?

                         

                        Kind Regards,

                         

                        Miles

                  • Re: Dates in Set Analysis
                    Kamal Naithani

                    Hi Miles,

                    Just try like this....

                    In case you want to calculate YTD-

                    =num(sum({<[Month]=,[Year]={$(=max([Year]))},[Date]=

                    {"<=$(vCurrDate)"}>}[Stock]))

                     

                    where vCurrDate is a varible and it's value will be --date(today()-1) or date (today())

                     

                    Again

                    for LYTD

                    =num(sum({<[Month]=,[Year]={$(=max([Year])-1)},[Date]=

                    {"<=$(vCurrDate)"}>}[Stock]))

                     

                     

                    Hope this help you

                    Regrads

                    Kamal

                      • Re: Dates in Set Analysis

                        Hi Kamal,

                         

                        That's great in terms of last 12 months to date, but in terms of getting the previous 12 months prior to that why is it when I change the formula to this:

                         

                        =num(sum({<[Month]=,[Year]={$(=max([Year])-2)},[Date]={"<=$(vPriorYearDate)"}>}quantity))

                         

                        Where vPriorYearDate is todays date minus 1 year,

                         

                        it doesn't work...any ideas?

                         

                        Kind Regards,

                         

                        Miles

                          • Re: Dates in Set Analysis
                            Kamal Naithani

                            Hi Miles,

                            You can use the addmonths() in case you want to get the sum for the Prior 12 months or more.

                             

                            //Num#(Sum({<YearMonth1 ={">=$(=Date(addmonths(Max(YearMonth1), -11), 'MMM-YY')) <=$(=Date(addmonths(Max(YearMonth1), 0),  'MMM-YY'))"} >}Quantity)).

                            This will give the sum for last one year sale from Todays date.

                            First Calculate YearMonth in the Script where you are calculating Year and other date field

                            (Month(date)&'-'& right(Year(date),2)) as YearMonth,

                            and than take the resident of that table,

                            and calculate

                            YearMonth1

                            Date(Date(Date#(YearMonth,'MMM-YY'),'DD-MM-YYYY'),'MMM-YY') as YearMonth1.

                            This is done because we want the YearMonth Format to be in number as used in the Front-End.

                             

                            Hope this help you

                             

                            Regards

                            Kamal