topic Re: month difference in QlikView

month difference

capriconuser — Sat, 16 Nov 2024 02:42:12 GMT

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

Re: month difference

ChennaiahNallani — Tue, 20 Aug 2019 06:09:36 GMT

Check below link

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

Re: month difference

MindaugasBacius — Tue, 20 Aug 2019 06:29:07 GMT

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?

Re: month difference

capriconuser — Tue, 20 Aug 2019 07:35:48 GMT

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 ?

Re: month difference

MindaugasBacius — Tue, 20 Aug 2019 07:40:59 GMT

Check your date as it goes to 9018 years.

Re: month difference

capriconuser — Tue, 20 Aug 2019 07:53:38 GMT

ohh

Re: month difference

capriconuser — Tue, 20 Aug 2019 07:56:33 GMT

how i remove " - " minus sign ?

Re: month difference

capriconuser — Tue, 20 Aug 2019 08:13:15 GMT

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..

Re: month difference

marcus_malinow — Tue, 20 Aug 2019 08:13:26 GMT

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

Re: month difference

capriconuser — Tue, 20 Aug 2019 09:46:52 GMT

i did not understand .. i need simple expression

Re: month difference

marcus_malinow — Tue, 20 Aug 2019 10:06:02 GMT

Ok, an explanation

First off I create a field named IntervalFormatted

This is calculated by taking Date To, formatted as YYYYMMDD, and subtracting Date From, with the same formatting. The result is formatted as '#00000' so we always have characters for YMMDD

For example

20180331 - 20180101 = 00230 (0 years, 02 months, 30 days)

20180312 - 20180115 = 00197

20190319 - 2018-01-20 = 10199

With this result, disregard the right 4 characters (i.e. extract the years) and multiply by 12 to get months

left(IntervalFormatted, len(IntervalFormatted) - 4)

Then take the months part and add these

left(right(IntervalFormatted, 4), 2)

If you still have issues understanding this I'd suggest playing with the code in a demo app. Try adding additional values in the inline table

Re: month difference

ChennaiahNallani — Tue, 20 Aug 2019 12:58:08 GMT

Try like below.

SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);

t:
Load * Inline [
fromdate, todate
01-02-2019, 02-02-2020
07-23-2019, 12-31-2019
07-28-2019, 12-31-2019
07-28-2019, 12-31-2019
08-04-2019, 12-31-2019
08-04-2019, 12-31-2019
08-08-2019, 12-31-2019
];

t1:
Load
date(Date#(fromdate,'MM-DD-YYYY'),'MM-DD-YYYY') as Fromdate1,
date(Date#(todate,'MM-DD-YYYY'),'MM-DD-YYYY') as todate1
Resident t;

Load
Fromdate1 as f,
todate1 as t,
($(MonthDiff(Fromdate1,todate1))) AS Difference
Resident t1;
Drop Table t1;
Drop Table t;