Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

how to find number of months when one of the date is system date/current date

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

5 Replies
vgutkovsky
Honored Contributor II

how to find number of months when one of the date is system date/current date

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

Not applicable

how to find number of months when one of the date is system date/current date

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

jason_michaelid
Honored Contributor II

how to find number of months when one of the date is system date/current date

You could try:

If(OpenDate >= AddMonths(Today(),-12) AND OpenDate >= AddMonths(Today(),-6),1,0) AS Flag

Hope this helps,

Jason

adnan_rafiq
Contributor II

how to find number of months when one of the date is system date/current date

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

Highlighted
jason_michaelid
Honored Contributor II

how to find number of months when one of the date is system date/current date

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

Community Browser