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

Dates not matching up in front and back end

First of all, I would like to express how much I despise how QlikView handles dates. The majority of my problems stem from date formatting and the stupid way scripting seems to handle it. Anyway, I have an issue right now where I should have a much greater count of rejoin dates based on the script below (see RejoinFlag line):

The only problem is that when the script runs I don't have the rejoin counts I should have:


If you look, alllllll those white 201403's on the right should be counted in the sum of rejoins on the left (notice I formatted them EXACTLY the same as I did in the script). Why can't the script compare the numbers casted as such even though they look identical on the front end?? Any help regarding this would be greatly appreciated.

Thanks,

Joe

1 Solution

Accepted Solutions

Re: Dates not matching up in front and back end

Assuming that SNAPSHOT_MONTH_ID is loaded as a real date -- not just a YYYYMM string -- you can compare the two using MonthStart() as I suggested.

MonthStart(SNAPSHOT_MONTH_ID) = MonthStart(REJOINDATE)


If SNAPSHOT_MONTH_ID is not a true date, but the actual string 201201, then you can do the comparison this way:


SNAPSHOT_MONTH_ID = text(date(REJOINDATE,'YYYYMM'))


-Rob

5 Replies
Not applicable

Re: Dates not matching up in front and back end

I figured out the problem but I still don't know how to fix it. the SNAPSHOT_MONTH_ID date is loaded in the format 'YYYYMM' but the REJOINDATE is loaded in the format of 'YYYYMMDD'... so it's only counting rejoins that happened on the first of that YYYYMM REJOINDATE. How can I truncate the days from rejoins so it doesn't count them when comparing it to the SNAPSHOT_MONTH_ID?

Thanks,

Joe

Re: Dates not matching up in front and back end

This is a common mis-understanding about dates (I know, frustrating). The Date() function does not cast. It does not change values. It is only a display formatting function. In the listbox, you have one line for each of those dates because the underlying values still have the day portion.

If you want to compare that two dates are in the same month a correct method would be:

MonthStart(date1) = MonthStart(date2)

See these series of excellent posts by hic on the subject of Dates.

The Date Function

http://community.qlik.com/blogs/qlikviewdesignblog/2012/06/07/get-the-dates-right

Primer for QlikView Date fields

-Rob

Not applicable

Re: Dates not matching up in front and back end

Thank you. Can you see my first response? Maybe you can elaborate on that as well.

Thanks,

Joe

Re: Dates not matching up in front and back end

Assuming that SNAPSHOT_MONTH_ID is loaded as a real date -- not just a YYYYMM string -- you can compare the two using MonthStart() as I suggested.

MonthStart(SNAPSHOT_MONTH_ID) = MonthStart(REJOINDATE)


If SNAPSHOT_MONTH_ID is not a true date, but the actual string 201201, then you can do the comparison this way:


SNAPSHOT_MONTH_ID = text(date(REJOINDATE,'YYYYMM'))


-Rob

Not applicable

Re: Dates not matching up in front and back end

That did it! Thank you, Mr. Wunderlich. You're a saint.

Thanks,

Joe

Community Browser