Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
capriconuser
Creator
Creator

month difference

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

 

Labels (1)
11 Replies
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

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
Creator
Creator
Author

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

from date  to date    difference

clipboard_image_0.png

 

 

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

MindaugasBacius
Partner - Specialist III
Partner - Specialist III

Check your date as it goes to 9018 years.

capriconuser
Creator
Creator
Author

ohh

capriconuser
Creator
Creator
Author

how i remove " - " minus sign ? 

capriconuser
Creator
Creator
Author

but i have some records like these 

From dateto dateDifference
7/23/201912/31/20190
7/28/201912/31/20190
7/28/201912/31/20190
8/4/201912/31/20190
8/4/201912/31/20190
8/8/201912/31/20190
5/8/201912/31/20190
1/1/201912/31/20190
2/18/201912/31/20190
5/14/201912/31/20190
7/28/201912/31/20190
3/15/201912/31/20190
6/9/201912/31/20190
1/16/20203/2/20200
1/8/20208/8/20200
   

 

and these show differences 0 which is incorrect..

marcus_malinow
Specialist III
Specialist III

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
Creator
Creator
Author

i did not understand .. i need simple expression