Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP & Luminary
MVP & Luminary

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
Highlighted
Not applicable

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

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Not applicable

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

Thanks,

Joe

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Not applicable

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

Thanks,

Joe