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.
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?
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.