topic Re: Help with Date format in QlikView

Help with Date format

Sat, 15 Mar 2014 02:45:18 GMT

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 — Sat, 15 Mar 2014 06:39:49 GMT

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

PrashantSangle — Sat, 15 Mar 2014 07:15:35 GMT

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.

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

stevedark — Sat, 15 Mar 2014 07:31:29 GMT

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

Re: Help with Date format

Sat, 15 Mar 2014 14:48:13 GMT

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

Re: Help with Date format

Sat, 15 Mar 2014 15:18:16 GMT

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

PrashantSangle — Sat, 15 Mar 2014 15:33:58 GMT

Hi,

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

Thanks and Regards,

Re: Help with Date format

Sat, 15 Mar 2014 16:25:07 GMT

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

Re: Help with Date format

Sat, 15 Mar 2014 16:44:29 GMT

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

Sat, 15 Mar 2014 16:46:04 GMT

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

SunilChauhan — Sat, 15 Mar 2014 16:46:13 GMT

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

stevedark — Sat, 15 Mar 2014 23:27:46 GMT

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