topic Re: Working with dates and auto increment in QlikView

Working with dates and auto increment

Mon, 29 Dec 2014 09:11:59 GMT

floor((Today() - Date([Date],'D-MMM-YYYY'))/365) &' Years, ' & floor(Frac((Today() - Date([Date],'D-MMM-YYYY'))/365)*12) &' Months, '& num(frac(Frac((Today() - Date([Date],'D-MMM-YYYY'))/365)*12)*30,'#,##0') & ' Days' as [Date spent]

Hello,

Check this code segment. Am calculating subtraction of a date from present date; Year, Month and Day But there is a problem dealing with Leap-Year, and Months having up to 31 days. How do i resolve this? Check code segment very well.

Re: Working with dates and auto increment

Gysbert_Wassenaar — Mon, 29 Dec 2014 10:13:50 GMT

Perhaps like this:

Year(Today()) - Year( [Date]) + (DayNumberOfYear(Today())<DayNumberOfYear([Date])) & ' Years' &

Month(Today()) - Month( [Date]) - 12 * (DayNumberOfYear(Today())<DayNumberOfYear([Date])) & ' Months' &

Day(Today()) - Day([Date]) - Day(MonthEnd([Date])) * (Day(Today())<Day([Date])) & ' Days' AS [Date Spent]

Re: Working with dates and auto increment

Mon, 29 Dec 2014 10:42:03 GMT

I implemented, attached didnt give what i wanted to achieve. [Date spent1] field worked but need to resolve the issues mention above. Find attached

Regards

Re: Working with dates and auto increment

Gysbert_Wassenaar — Mon, 29 Dec 2014 15:32:54 GMT

See attached qvw.