11 Replies Latest reply: Mar 15, 2014 7:27 PM by Steve Dark RSS

    Help with Date format

    Deborah Pyykkonen

      Can anyone see anything clearl wrong with this script and why it appears to be treating my date as a string?

       

      The code is creating a Weekend date based on a Thursday weekend.  The logic is working but when I try to use this in a variable in set analysis it wont recognize it as a date even if I try to format it.  But I created the same code converting to a NUM() and it shows the correct number for each date.  In my set analysis I am using the max() function to create a variable of the max available week selected then I need to use that in set analysis to compare to the field below but it wont recognize it.  No error, it just ignores it.  Frustrating but I havent worked with date variables in quite a while.

       

      if(TempDate > weekend(TempDate)-3,

                       date(floor(weekend(TempDate))+4,'M/D/YYYY'),

                       date(floor(weekend(TempDate)-3),'M/D/YYYY'))

                      

                        as THU_WeekEnd,

        • Re: Help with Date format
          jagan mohan rao appala

          Hi,

           

          In variable if you use Max(Date) will return the date in numeric format, in set analysis date formats should be same.  You convert the numeric date into date by using this

           

          =Date(Max(Date))

           

          This should work, if not check the format.

           

          Regards,

          Jagan.

          • Re: Help with Date format
            Prashant Sangle

            Hi,

             

            this several options,

            1.You can change above script to

             

            if(TempDate > weekend(TempDate)-3,

                             date(date#(floor(weekend(TempDate))+4,'M/D/YYYY'),'M/D/YYYY'),

                             date(date#(floor(weekend(TempDate)-3),'M/D/YYYY'),'M/D/YYYY'))

                          

                              as THU_WeekEnd,

            and in your set analysis use

             

            Date(Max(Date)) //Date field should be date fieldname it is case sensitive.

             

            2.

            If you are using your script using Num()

            then in your set analysis

            You have to compare it with

            Num(Max(Date))  //Date field should be date fieldname it is case sensitive. 

              • Re: Help with Date format
                Deborah Pyykkonen

                Your option #1 does not return any values for the WeekEnd field and I tried option 2 previously.  This one is a mystery to me.  Very frustrating.  I am wondering if the weekend function is causing the problem.  the %DateKey field works using the same TempDate , it is just the Thu_WeekEnd field that doesnt work and only difference is the WeekEnd() function.

                  • Re: Help with Date format
                    Prashant Sangle

                    Hi,

                     

                    If possible can you share your app with some sample data.

                     

                    Thanks and Regards,

                      • Re: Help with Date format
                        Deborah Pyykkonen

                        Due to the sensitive data I am not able to without a lot of work up front.  Does this help:

                         

                        Script Statement in MasterCalendar linked to Fact table by %DateKey.  I changed it to eliminate the Weekend() function:

                         

                          If(WeekDay(TempDate) = 'Thu', num(floor(TempDate)),

                                    If(WeekDay(TempDate) = 'Fri', num(floor(TempDate))+6,  

                                    If(WeekDay(TempDate) = 'Sat', num(floor(TempDate))+5, 

                                    If(WeekDay(TempDate) = 'Sun', num(floor(TempDate))+4,

                                    If(WeekDay(TempDate) = 'Mon', num(floor(TempDate))+3,

                                    If(WeekDay(TempDate) = 'Tue', num(floor(TempDate))+2,

                                    If(WeekDay(TempDate) = 'Wed', num(floor(TempDate))+1))))))) as Thu_WeekEnd      ,

                         

                        Now the WeekEnd Field is showing as  i.e. 41711

                         

                        Expression (I gave up on using variable for now)

                         

                        =sum({<Thu_WeekEnd ={$(=max(Thu_WeekEnd)-7)},[GL Account Type] = {'Income'},JournalHdr = {'Sales Journal'}>}

                         

                        User selects a date in the Thu_WeekEnd field i.e. 41704

                        Result of max() in set analysis = 41697 (proven in text box)

                         

                        Result of above sums the values for the 41704 week NOT the 41697 week (prior week)

                         

                        Not sure what I am missing.  I am simply trying to show the week over week change based on the user selections

                         

                         

                        [Tran Detail Amt])

                         

                         

                         

                         

                        [Tran Detail Amt])

                          • Re: Help with Date format
                            Deborah Pyykkonen

                            something tells me I am missing something obvious.  I have never had this much trouble on this type of analysis before.

                            • Re: Help with Date format
                              Deborah Pyykkonen

                              It is almost as if it is completely ignoring the max portion of the set analysis.  When user clears selection for weekend, it sums everything

                              • Re: Re: Help with Date format
                                Sunil Chauhan

                                Hi .I things its looking correct to me

                                 

                                if you convert numbers into date then it will be Previous weekend you want

                                see the attached file

                                 

                                In my example i have selected 13/03/2013 and previous weekend it showing as 06/03/2013.

                                 

                                Hope this helps

                                • Re: Help with Date format
                                  Steve Dark

                                  Because you are using the Floor statement this is getting converted to an integer field.  If you replace Floor with DayStart and remove the Num function this should return a date in your default date format.

                                   

                                  Rather than relying on default formats though you can always force the format with a Date function.  Wrap your entire statement in a Date function and it will cause it to display as you want it (remember that dates are only ever numbers - it's only how they display that changes).  The code would be:

                                   

                                    Date(If(WeekDay(TempDate) = 'Thu', num(floor(TempDate)),

                                              If(WeekDay(TempDate) = 'Fri', num(floor(TempDate))+6, 

                                              If(WeekDay(TempDate) = 'Sat', num(floor(TempDate))+5,

                                              If(WeekDay(TempDate) = 'Sun', num(floor(TempDate))+4,

                                              If(WeekDay(TempDate) = 'Mon', num(floor(TempDate))+3,

                                              If(WeekDay(TempDate) = 'Tue', num(floor(TempDate))+2,

                                              If(WeekDay(TempDate) = 'Wed', num(floor(TempDate))+1))))))), 'M/D/YYYY') as Thu_WeekEnd,

                                   

                                  You could then use the code I posted above in your variable for getting the max date:


                                  =Date(Max(THU_WeekEnd), 'M/D/YYYY')

                                   

                                  This will then give a date in a matching format.  Once your date formats match then Set Analysis should start behaving itself.

                                   

                                  Steve

                          • Re: Help with Date format
                            Steve Dark

                            Set Analysis is dependant on the format of the date as well as the value.  This seems a bit odd to me, but it is the case.  As Jagan suggests you need to format the date that you are pushing into Set Analysis, but as you are specifying a fixed format in your load script you need to specify exactly the same format in Set Analysis, eg:

                             

                            =Date(Max(THU_WeekEnd), 'M/D/YYYY')

                             

                            This should then work for you.

                             

                            Steve