topic Re: help with Expression in QlikView

help with Expression

arusanah — Wed, 01 Jul 2015 14:57:06 GMT

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!

Re: help with Expression

swuehl — Wed, 01 Jul 2015 15:01:20 GMT

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
];

Re: help with Expression

swuehl — Wed, 01 Jul 2015 15:04:52 GMT

An approximation to month difference could also be achieved by

=(vToDate- vFromDate+1) / 30.5

Re: help with Expression

stigchel — Wed, 01 Jul 2015 15:17:31 GMT

You can calculate the difference in months like this:

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

Re: help with Expression

pratap6699 — Wed, 01 Jul 2015 15:25:48 GMT

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

Re: help with Expression

arusanah — Wed, 01 Jul 2015 16:22:55 GMT

Thank you so much Swuehl,

Simple , short And perfect solution

Re: help with Expression

arusanah — Wed, 01 Jul 2015 16:24:36 GMT

Thanks Piet ,

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

Re: help with Expression

stigchel — Wed, 01 Jul 2015 16:30:31 GMT

Glad to be of help