Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
MuraliPrasath
Creator III
Creator III

Date Difference

Hi,

I need to find out each contract delay in months based on "old date" and "new date". 

The given example:  I need Month Count difference from "First old date" to "Last New Date" by Contract ID's. 

Difference from 2020-11-12 to 2021-04-17 in Month Count measure needed. 

 

Contract IDContract Edit DateOld DateNew Date
12020-11-012020-11-122021-01-10
12021-01-122021-01-102021-02-10
12021-02-032021-02-102021-03-15
12021-03-242021-03-152021-04-17
Labels (3)
1 Solution

Accepted Solutions
abhijitnalekar
Specialist II
Specialist II

Hi @MuraliPrasath ,

 

Please create the first two dimensions as below

VOldDate : aggr(min([Old Date]),[Contract ID])

VNewDate : aggr(max([New Date]),[Contract ID])

and then create below measure to get the months between old date and new date

((year((aggr(max([New Date]),[Contract ID])))*12)+month(Date#((aggr(max([New Date]),[Contract ID])))))

- (((year((aggr(min([Old Date]),[Contract ID])))*12)+month((aggr(min([Old Date]),[Contract ID])))))

 

Regards,
Abhijit
keep Qliking...

View solution in original post

2 Replies
abhijitnalekar
Specialist II
Specialist II

Hi @MuraliPrasath ,

 

Please create the first two dimensions as below

VOldDate : aggr(min([Old Date]),[Contract ID])

VNewDate : aggr(max([New Date]),[Contract ID])

and then create below measure to get the months between old date and new date

((year((aggr(max([New Date]),[Contract ID])))*12)+month(Date#((aggr(max([New Date]),[Contract ID])))))

- (((year((aggr(min([Old Date]),[Contract ID])))*12)+month((aggr(min([Old Date]),[Contract ID])))))

 

Regards,
Abhijit
keep Qliking...
MuraliPrasath
Creator III
Creator III
Author

Hi, Thanks for quick reply, Will apply this logic and keep you posted.