Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
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

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