Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Help with Date format

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,

11 Replies
MVP
MVP

Re: Help with Date format

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

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. 

Great dreamer's dreams never fulfilled, they are always transcended.
MVP
MVP

Re: Help with Date format

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

Not applicable

Re: Help with Date format

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

Not applicable

Re: Help with Date format

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

Hi,

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

Thanks and Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Not applicable

Re: Help with Date format

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

Not applicable

Re: Help with Date format

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

Not applicable

Re: Help with Date format

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

Community Browser