Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
arusanah
Creator II
Creator II

help with Expression

I have two Variables vFromDate & vToDate.  My requirement is to find num of months between two variables . I'm writing  expression

=(num(month(vToDate))-num(month(vFromDate)))+1. which works fine as long as year is same . e.g 1/1/2014 - 7/31/2014 . but the minute i change date to different yr e.g 1/1/2013 - 7/31/2014 .. the above expression fails .

I tried another expression

=num(Date(vToDate))-num(Date(vFromDate))+1

this gives me no of days . so now i need to convert those back to no of months

e.g total no of days is 577 and if converted to month should give me 19 months .

pls help!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

An approximation to month difference could also be achieved by

=(vToDate- vFromDate+1) / 30.5

View solution in original post

7 Replies
swuehl
MVP
MVP

Maybe something like this will help you:

SET DateFormat='DD/MM/YYYY';

INPUT:

LOAD *

, if(day(VisitDay) >= day(DoB),

  mod(MonthIDVisitDay-MonthIDDoB,12),

  if(mod(MonthIDVisitDay-MonthIDDoB,12)=0,11,mod(MonthIDVisitDay-MonthIDDoB,12)-1)) as AgeMonths

, if(day(VisitDay) >= day(DoB),

  day(VisitDay)-day(DoB),

  VisitDay-addmonths(DoB,12*AgeYears+if(mod(MonthIDVisitDay-MonthIDDoB,12)=0,11,mod(MonthIDVisitDay-MonthIDDoB,12)-1))) as AgeDays

;

LOAD *

, age(VisitDay,DoB) as AgeYears

, Year(DoB)*12+Month(DoB) as MonthIDDoB

, Year(VisitDay)*12+Month(VisitDay) as MonthIDVisitDay

INLINE [

DoB, VisitDay

01/01/2011, 11/03/2012

01/01/2010, 29/02/2012

01/03/2010, 29/02/2012

31/01/2011, 01/02/2011

28/02/2000, 01/03/2012

28/02/2000, 01/03/2011

29/02/2008, 19/02/2012

14/05/2012, 02/09/2013

31/01/2013, 30/01/2014

];

swuehl
MVP
MVP

An approximation to month difference could also be achieved by

=(vToDate- vFromDate+1) / 30.5

View solution in original post

stigchel
Partner
Partner

You can calculate the difference in months like this:

=Num(((year(vToDate) * 12) + month(vToDate)) - (((year(vFromDate) * 12) + month(vFromDate))) + 1)

pratap6699
Creator
Creator

=num(Month(Vtodate))-num(Month(Vfromdate))

arusanah
Creator II
Creator II
Author

Thank you so much Swuehl,

Simple , short And perfect  solution

arusanah
Creator II
Creator II
Author

Thanks Piet ,

Your Expression was perfect too .. unfortunately i can mark only one as correct answer .. But thank you so much for your help!!

stigchel
Partner
Partner

Glad to be of help