Date Name Problem

[gerhardl]

Hello,

I'm faving a weird problem with dates.

All our Transactions have a field called Posted Date and another field called Effective Date. In QlikView I load them like this, from daily text files:

DATE(DATE#( [Posted Date], 'DD-MMM-YY')) as [Posted Date],

DATE(DATE#( left([Effective Date],11), 'DD-MMM-YY')) as [Effective Date],

We run our billing cycle on the 10th of every month. So I want to give each Transaction a date matching the cycle the transaction will be billed. If it is between 11 October and 10 November, it will be billed on 10 November (November Cycle). If it is on 11 November it will be billed in December Cycle.

So I do this:

if(day(DATE(DATE#( [Posted Date], 'DD-MMM-YY')))<11,month(DATE(DATE#( [Posted Date], 'DD-MMM-YY'))),month(DATE(DATE#( [Posted Date], 'DD-MMM-YY')))+1) as [Posted Cycle],

if(day(DATE(DATE#( [Effective Date], 'DD-MMM-YY')))<11,month(DATE(DATE#( [Effective Date], 'DD-MMM-YY'))),month(DATE(DATE#( [Effective Date], 'DD-MMM-YY')))+1) as [Effective Cycle],

For some reason this is giving me the correct answers, but the answers are only in the correct format for certain months. Please see the attached picture. For months like June, July and September, it shows the month NUMBER and not name. This seems to only be a problem with EFFECTIVE date, even thought it is exactly the same as posted date (the format and load expression).

Any ideas?

Gerhard

[#2 · Accepted solution]

the else condition of ur Effective Cycle field shud b modified as

month(date#(month(date(DATE#([Effective Date] ,'DD.MM.YY')))+1,'M'))

Hope it helps!!!

[gerhardl]

I think I am losing my mind.

That works, but I do not understand why. The attached picture shows the table with the results of my expression, and next to it the result of yours.

It also shows the Day of theTransaction - the ELSE condition only comes into play if the DAY is > 10, yet your change (which is only on the ELSE condition) fixes rows where the DAY is < 11 and > 10.

It is late Friday afternoon here, so I am probably just being stupid.

Thanks for the fix... but explain what is going on if you don;t mind

[#4]

in ur 1st post u've used the condition  day<11 which means else part comes into play when day>=11 which means for day=10 also the if condition rules (& not the else condition) which only returns the Month of effective date.

hope it is clear now !!!