topic Re: Date/month calculation in QlikView

Date/month calculation

karan_kn — Wed, 25 Nov 2020 16:16:04 GMT

The highlighted months are showing 13-36 month, can someone help me to fix this.

Re: Date/month calculation

sunny_talwar — Thu, 22 Mar 2018 18:23:39 GMT

I think you were doing Month comparison in the script.... try this

T1:

LOAD *,

If(Len(Trim(Previous_Date)) = 0, 'New',

If(AddYears(Previous_Date, 1) >= Current_Date, '1-12 month',

If(AddYears(Previous_Date, 3) >= Current_Date, '13-36 month',

If(AddYears(Previous_Date, 5) >= Current_Date, '37-60 month')))) as Status;

LOAD * INLINE [

ID, Current_Date, Previous_Date

1, 03/22/2018, 02/27/2017

2, 03/22/2018, 02/28/2017

3, 03/22/2018, 02/27/2017

4, 03/22/2018, 03/03/2017

5, 03/22/2018, 03/03/2017

6, 03/22/2018, 03/07/2017

7, 03/22/2018, 03/01/2017

8, 03/22/2018, 04/28/2017

9, 03/22/2018, 03/22/2017

];

Re: Date/month calculation

sunny_talwar — Thu, 22 Mar 2018 18:25:42 GMT

Modified the script to see the other two options

T1:

LOAD *,

If(Len(Trim(Previous_Date)) = 0, 'New',

If(AddYears(Previous_Date, 1) >= Current_Date, '1-12 month',

If(AddYears(Previous_Date, 3) >= Current_Date, '13-36 month',

If(AddYears(Previous_Date, 5) >= Current_Date, '37-60 month')))) as Status;

LOAD * INLINE [

ID, Current_Date, Previous_Date

1, 03/22/2018, 02/27/2017

2, 03/22/2018, 02/28/2017

3, 03/22/2018, 02/27/2017

4, 03/22/2018, 03/03/2017

5, 03/22/2018, 03/03/2017

6, 03/22/2018, 03/07/2017

7, 03/22/2018, 03/01/2017

8, 03/22/2018, 04/28/2017

9, 03/22/2018, 03/22/2017

10, 03/22/2018,

11, 03/22/2018, 03/22/2014

];

Re: Date/month calculation

swuehl — Thu, 22 Mar 2018 18:28:03 GMT

And maybe catch also the older values:

If(Len(Trim(Previous_Date)) = 0, 'New',

If(AddYears(Previous_Date, 1) >= Current_Date, '1-12 month',

If(AddYears(Previous_Date, 3) >= Current_Date, '13-36 month',

If(AddYears(Previous_Date, 5) >= Current_Date, '37-60 month', 'even older than 60 months'))))

Re: Date/month calculation

sunny_talwar — Thu, 22 Mar 2018 18:29:50 GMT

Good catch ... Always good to have a fall back statement to avoid nulls. Thanks Stefan!!

Re: Date/month calculation

karan_kn — Fri, 23 Mar 2018 03:32:58 GMT

Thanks Sunny and Stefan

Re: Date/month calculation

rajaxavier — Thu, 30 Apr 2020 12:29:51 GMT

Tab:

load *,
if(interval(Current_Date-Previous_Date,'DD')<=365,'1-12 Month',
if(interval(Current_Date-Previous_Date,'DD')<=1095,'13-36 Month',
if(interval(Current_Date-Previous_Date,'DD')<=1825,'37-60 Month',
if(isnull(interval(Current_Date-Previous_Date,'DD')),'New'))))as Status,
if(isnull(interval(Current_Date-Previous_Date,'DD')),0,interval(Current_Date-Previous_Date,'DD')) as DaysDiff;
LOAD * INLINE [

ID, Current_Date, Previous_Date

1, 03/22/2018, 02/27/2017

2, 03/22/2018, 02/28/2017

3, 03/22/2018, 02/27/2017

4, 03/22/2018, 03/03/2017

5, 03/22/2018, 03/03/2017

6, 03/22/2018, 03/07/2017

7, 03/22/2018, 03/01/2017

8, 03/22/2018, 04/28/2017

9, 03/22/2018, 03/22/2017

10, 03/22/2018,

11, 03/22/2018, 03/22/2014
];