month difference

[capriconuser]

hi 

there is 2 dates from date and to date .. and i want to get month difference from these two dates 

like if 

from date      to date        month difference

01-02-2019  02-02-2020           13 

 

i tried this but this shows wrong results 

month(from date) - month(to date) 

and i also try this 

month([from date] - [to date]) 

[ChennaiahNallani]

Check below link

https://community.qlik.com/t5/QlikView-Documents/Calculating-Months-difference-between-two-dates/ta-...

[MindaugasBacius]

See if this works for you:

year(from date) * 12 + month(from date) - year(to date) * 12 - month(to date)

The logic is simple here - you're creating the date 2019-02-01 to months as well as 2020-02-02.

2019-02-01 becomes 24230

2020-02-02 becomes 24242

The difference between them is 24242-24230=12 months.

I guess this is what you expect to get, isn't it?

[capriconuser]

When i tried your expression .. i have one of the record like this 

from date  to date    difference

and this is showing difference like -84,007 .. how is that possible ?

[MindaugasBacius]

Check your date as it goes to 9018 years.

[capriconuser]

ohh

[capriconuser]

how i remove " - " minus sign ? 

[capriconuser]

but i have some records like these 

From date	to date	Difference
7/23/2019	12/31/2019	0
7/28/2019	12/31/2019	0
7/28/2019	12/31/2019	0
8/4/2019	12/31/2019	0
8/4/2019	12/31/2019	0
8/8/2019	12/31/2019	0
5/8/2019	12/31/2019	0
1/1/2019	12/31/2019	0
2/18/2019	12/31/2019	0
5/14/2019	12/31/2019	0
7/28/2019	12/31/2019	0
3/15/2019	12/31/2019	0
6/9/2019	12/31/2019	0
1/16/2020	3/2/2020	0
1/8/2020	8/8/2020	0

and these show differences 0 which is incorrect..

[marcus_malinow]

A little script to show you how I might tackle this.

This takes into account days, so 

2018-03-01 - 2018-01-20 would return 1 (complete month)

LOAD
[Date From],
[Date To],
IntervalFormatted,

(left(IntervalFormatted, len(IntervalFormatted) - 4) * 12)
+
left(right(IntervalFormatted, 4), 2)
as IntervalMonths
;

LOAD
[Date From],
[Date To],
Num(
(Year([Date To]) & Num(Month([Date To]), '00') & Num(Day([Date To]), '00'))
-
(Year([Date From]) & Num(Month([Date From]), '00') & Num(Day([Date From]), '00') )
, '#00000')
as IntervalFormatted
;

LOAD * INLINE [
Date From, Date To
2018-01-01, 2018-03-31
2018-01-20, 2019-03-19
2018-01-15, 2018-03-12
];

[capriconuser]

i did not understand .. i need simple expression