topic subtract month in QlikView

subtract month

Tue, 12 Oct 2010 17:15:13 GMT

How can I Substract 2 dates and only get the months

for example : (DD/MM/YYYY)

01/01/2010 - 01/01/2009 = 12

01/01/2010 - 01/01/2008 = 24

01/01/2010 - 15/01/2009 = 12

01/02/2010 - 01/06/2009 = 8

subtract month

pover — Tue, 12 Oct 2010 17:52:43 GMT

This has some degree of inexactitud, but should work for the next 100 years well.

=12*div((makedate(2010,2,1)-makedate(2009,6,1)),365)

floor(mod((makedate(2010,2,1)-makedate(2009,6,1)),365)/28)

Regards.

subtract month

Tue, 12 Oct 2010 17:53:45 GMT

Hi there, try this:

floor

((monthstart(date#('01/01/2010','DD/MM/YYYY')) - monthstart(date#('15/01/2009','DD/MM/YYYY' ))) /30

)

Regards

subtract month

Tue, 12 Oct 2010 18:20:49 GMT

Hi again, Karl is right about precision, so I realized there's no need to do the calcaulation in terms of days, but only months, hence you can use the following expression, without any fear of loosing precision in any point of the upcoming years:

=( ( year(makedate(2008,2,1)) - year(makedate(2006,8,1)) ) + ( month(makedate(2008,2,1)) - month(makedate(2006,8,1)) ) /12 ) *12

Best Regards

Re:subtract month

pover — Tue, 12 Oct 2010 19:02:04 GMT

I agree.

subtract month

rwunderlich — Tue, 12 Oct 2010 21:10:09 GMT

(year(date2) + month(date2)) - (year(date1) + month(date1))

subtract month

Tue, 12 Oct 2010 21:38:28 GMT


Rob Wunderlich wrote:
(year(date2) + month(date2)) - (year(date1) + month(date1)) <div></div>

I guess what Rob tried to do is simplify the formula, and yes indeed it can be simplified as:

=( year(makedate(2008,2,1)) - year(makedate(2006,8,1)) ) *12 + ( month(makedate(2008,2,1)) - month(makedate(2006,8,1)) )

subtract month

Wed, 13 Oct 2010 19:10:34 GMT

thanks a lot to all the members