Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

5 Replies
Not applicable
Author

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Author

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

Thanks,

Joe

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Author

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

Thanks,

Joe