Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I need to count the number of months between 2 dates. the first date is a field called OPEN_DATE and the second date is the Current system date/Current date vice versa.
My logic needs to work as
If count(month (OPEN DATE- CURRENT DATE))>=6 or count(month(OPEN DATE-CURRENT DATE))<=12 , 1,0 as FLAG
I would be using this logic in my Load script where I get the field OPEN DATE from an excel source. In the above expression 6 & 12 shows that anything which has a count between 6 & 12 months should have the value 1. Current date is the current date of that day when I am loading the script.
Please help me write this logic in Qlikview code. Appreciate all your help In Advance.
Thanks,
Tutan
In general you would use function today() or now(), depending on the level of detail you need. But you need to define what a "month" is--is it 30 days? Is 02/13/2012 - 01/02/2012 1 month or 2?
The result of subtracting 2 dates will be an integer (possibly with decimal precision). You would then need to decide how to use that integer to define the # of months.
Regards,
Vlad
Hi Vlad ,
My expectation is that when I subtract 2 dates, I get a count of month/months i.e values like 4 or 6 or anything which gives me the count of the number of months.
Thanks,
Tutan
You could try:
If(OpenDate >= AddMonths(Today(),-12) AND OpenDate >= AddMonths(Today(),-6),1,0) AS Flag
Hope this helps,
Jason
Tutan,
you can try this in order be more closer to actual Calender month passed.
(Month(Today()-date)+((Year(Today()-date)-1900)*12)-1+ day(Today()-date)/31)
It is calculating months based on assumption of first 6 Months of calendar.
Regards
If you just want to test for between 6 and 12 months then I think my solution should work off you. If you want an accurate calculation then Rob Wunderlich suggested a very nifty solution on a previous thread:
http://community.qlik.com/thread/47580?start=0&tstart=0
Jason