Year Month Days

[#1]

Hi ALL,

I want the output like this 2 Year 3 Month 5 Days in an Expression Can any one tell how to find the Year Months and Days in the Expression.

Any Help would be appreciated.

Regards

Deepak

[Gysbert_Wassenaar]

Which expression? Will any expression do?

=1+1 & ' Year ' & 1+2 & ' Month ' & 5/1 & ' Days'

[#3]

hi ,

I am using below expression to find out the item which are not issued last n days . So I want Output like 2 Year 2 Month 1 Days.

Please look this expression.

=if([Stock Qty]>0,if((floor((IF((TODAY()-IF(IsNull([Issued Qty])<0,

(if(sum({<FiscalYear=,Month=,Day=>}[Issued Qty])<0,date(max({<FiscalYear=,Month=,Day=>}%Link_Date)))),

(if(sum({<FiscalYear=,Month=,Day=>}[Recieved Qty])>0,date(min({<FiscalYear=,Month=,Day=>}%Link_Date))))))>$(vDay),(TODAY()-IF(IsNull( [Issued Qty])<0,

(if(sum({<FiscalYear=,Month=,Day=>}[Issued Qty])<0,date(max({<FiscalYear=,Month=,Day=>}%Link_Date)))),

(if(sum({<FiscalYear=,Month=,Day=>}[Issued Qty])<0,date(max({<FiscalYear=,Month=,Day=>}%Link_Date))))))))

/365)&' Year '&floor((Mod((IF((TODAY()-IF(IsNull( [Issued Qty])<0,

(if(sum({<FiscalYear=,Month=,Day=>}[Issued Qty])<0,date(max({<FiscalYear=,Month=,Day=>}%Link_Date)))),

(if(sum({<FiscalYear=,Month=,Day=>}[Issued Qty])<0,date(max({<FiscalYear=,Month=,Day=>}%Link_Date))))))>$(vDay),(TODAY()-IF(IsNull( [Issued Qty])<0,

(if(sum({<FiscalYear=,Month=,Day=>}[Issued Qty])<0,date(max({<FiscalYear=,Month=,Day=>}%Link_Date)))),

(if(sum({<FiscalYear=,Month=,Day=>}[Issued Qty])<0,date(max({<FiscalYear=,Month=,Day=>}%Link_Date))))))))

,365))/30)&' Month '&(frac((Mod((IF((TODAY()-IF(IsNull( [Issued Qty])<0,

(if(sum({<FiscalYear=,Month=,Day=>}[Issued Qty])<0,date(max({<FiscalYear=,Month=,Day=>}%Link_Date)))),

(if(sum({<FiscalYear=,Month=,Day=>}[Issued Qty])<0,date(max({<FiscalYear=,Month=,Day=>}%Link_Date))))))>$(vDay),(TODAY()-IF(IsNull( [Issued Qty])<0,

(if(sum({<FiscalYear=,Month=,Day=>}[Issued Qty])<0,date(max({<FiscalYear=,Month=,Day=>}%Link_Date)))),

(if(sum({<FiscalYear=,Month=,Day=>}[Issued Qty])<0,date(max({<FiscalYear=,Month=,Day=>}%Link_Date))))))))

,365))/30)*30)&' Days ')=' Year  Month  Days ','0',(floor((IF((TODAY()-IF(IsNull( [Issued Qty])<0,

(if(sum({<FiscalYear=,Month=,Day=>}[Issued Qty])<0,date(max({<FiscalYear=,Month=,Day=>}%Link_Date)))),

(if(sum({<FiscalYear=,Month=,Day=>}[Issued Qty])<0,date(max({<FiscalYear=,Month=,Day=>}%Link_Date))))))>$(vDay),(TODAY()-IF(IsNull( [Issued Qty])<0,

(if(sum({<FiscalYear=,Month=,Day=>}[Issued Qty])<0,date(max({<FiscalYear=,Month=,Day=>}%Link_Date)))),

(if(sum({<FiscalYear=,Month=,Day=>}[Issued Qty])<0,date(max({<FiscalYear=,Month=,Day=>}%Link_Date))))))))

/365)&' Year '&floor((Mod((IF((TODAY()-IF(IsNull( [Issued Qty])<0,

(if(sum({<FiscalYear=,Month=,Day=>}[Issued Qty])<0,date(max({<FiscalYear=,Month=,Day=>}%Link_Date)))),

(if(sum({<FiscalYear=,Month=,Day=>}[Issued Qty])<0,date(max({<FiscalYear=,Month=,Day=>}%Link_Date))))))>$(vDay),(TODAY()-IF(IsNull( [Issued Qty])<0,

(if(sum({<FiscalYear=,Month=,Day=>}[Issued Qty])<0,date(max({<FiscalYear=,Month=,Day=>}%Link_Date)))),

(if(sum({<FiscalYear=,Month=,Day=>}[Issued Qty])<0,date(max({<FiscalYear=,Month=,Day=>}%Link_Date))))))))

,365))/30)&' Month '&(frac((Mod((IF((TODAY()-IF(IsNull( [Issued Qty])<0,

(if(sum({<FiscalYear=,Month=,Day=>}[Issued Qty])<0,date(max({<FiscalYear=,Month=,Day=>}%Link_Date)))),

(if(sum({<FiscalYear=,Month=,Day=>}[Issued Qty])<0,date(max({<FiscalYear=,Month=,Day=>}%Link_Date))))))>$(vDay),(TODAY()-IF(IsNull( [Issued Qty])<0,

(if(sum({<FiscalYear=,Month=,Day=>}[Issued Qty])<0,date(max({<FiscalYear=,Month=,Day=>}%Link_Date)))),

(if(sum({<FiscalYear=,Month=,Day=>}[Issued Qty])<0,date(max({<FiscalYear=,Month=,Day=>}%Link_Date))))))))

,365))/30)*30)&' Days ')))

Regards

Deepak

[Anonymous]

I don't believe that there is an Interval "formatting" option or Qlikview function() which will return the result of an expression in a chart in the format "2 Year 3 Month 5 Days" you are looking for.

However, you may be able to create this format in the chart expression itself by concatenating calculated values and text.

For Example:

    if (EndDate - StartDate > 365, (ceil(EndDate,365) - ceil(StartDate,365))/365 & ' Years','') & ' '&
          (right(floor(EndDate),3) - right(floor(StartDate),3) - if(EndDate - StartDate > 365, (ceil(EndDate,365) - ceil(StartDate,365)))) & ' Days'

Where StartDate is 01/01/2011 & EndDate is 03/01/2012, this returns "1 Years & 2 Days".

It will be challenging to build the expression, especially when you try to deal with leap years!  Maybe there is a function you can call.  Good luck!

[tresesco]

Try something like :

Floor((today()-FromDate)/365) & '  Years ' & Floor(Mod((today()-FromDate),365)/12)&'  Month ' & Mod(Mod((today()-FromDate),365),30)&'  Days'   AS Age