Help with Date format

[#1]

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,

[jagan]

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.

[PrashantSangle]

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. 

[stevedark]

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

[#5]

unfortunately I tried that but same problem.  format of date and variable are the same.

[#6]

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.

[PrashantSangle]

Hi,

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

Thanks and Regards,

[#8]

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])

[#9]

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

[#10]

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