Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
selvakumara
Creator
Creator

Get number of Years,Months,Days

 

Team,

 

I want to get Days, Months, Years from one of my date.  Admission Date field value will be this format 20201001. 

 

But months is not calculating correctly. Instead of 4 months it will give 8 months. Please help me on this.

 

=IF

(Today() <= AddYears(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY'),1),'0 Years ',

Age(Today(),Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY')) & ' Years ')

&

IF(MONTH(Today()) = MONTH(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY')),'0 Months ',

IF(DAY(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY')) < DAY(Today()), fabs(MONTH(Today())-Month(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY'))) & ' Months ',

((MONTH(Today()))-Month(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY')))-1 & ' Months '

))

&

IF(MONTH(Today()) = MONTH(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY')),DAY(Today()) - DAY(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY')) & ' Days',

IF(DAY(Today()) > DAY(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY')),DAY(Today()) - DAY(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY')) & ' Days',

DAY(DATE(Interval(Today() - Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY'))))+1 & ' Days'

))

 

selvakumara_0-1615469814548.png

 

 

Thanks in advance.

 

Thanks

 

1 Reply
salonicdk28
Creator
Creator

Try this in your expression-

=div(year(Today())*12+month(Today()) - (year(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY'))*12
+month(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY'))),12) & ' Years'

&' '&

mod((year(Today())*12)+month(Today())-(year(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY'))*12+month(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY'))),12) & ' Months'

&' '&

(Today()-AddMonths(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY'),((year(Today())*12)+month(Today())) - (year(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY'))*12
+month(Date(Date#([Admission Date],'YYYYMMDD'),'MM/DD/YYYY'))))) & ' Days'