topic Re: Add number to a date in QlikView

Add number to a date

Wed, 25 Nov 2020 16:16:04 GMT

Hello ,

I am trying to add a number stored in a variable or a column in the table to a date and this is to calculate the Sum for prior year.

But none of the below seems to work,

Date(MyDate+$(vNoofDays))

Date(Date(MyDate,'MM/DD/YYYY')+ColumnName)

Date(Date(MyDate,'MM/DD/YYYY')+ColumnName,'MM/DD/YYYY')

However, if i tried to add a number, it works.

Please help.

Re: Add number to a date

sunny_talwar — Thu, 21 Jul 2016 20:31:57 GMT

Is your date correctly interpreted as date field? Check these links out:

Get the Dates Right

Why don’t my dates work?

and then once those are fixed, you might be able to just do this:

Date(MyDate + vNoofDays)

Re: Add number to a date

Thu, 21 Jul 2016 21:00:47 GMT

Thanks for your reply Sunny. What i am trying to achieve here is the comparison between Sales of Current Year with prior Year. And for Current Year,

YTD is

=Sum({<FISCALYEAR={"$(=YearofToday)"},DateNum={">=$(=Num(FISCALYEARSTART))<=$(=Num(Today()))"}>} Sales)

But for prior year, i want the Sales to be calculated for the same number of days of the Current Year for which the YTD was calculated , but for the Proevious year. Eg: Assume, CY YTD the no of days is 200 from the FISCAL Year STRT to today.

So, my Prior Year YTD should start from 2015's FISCAL Year START and add the no of days 200, and sum the Sales.

I am stuck at adding the no of days and it is not working.

Re: Add number to a date

tyagishaila — Fri, 22 Jul 2016 06:56:12 GMT

It should be,

Dim: YourDateField

Expression: Date( YourDateField + vNoofdays)

Re: Add number to a date

anagharao — Fri, 22 Jul 2016 07:05:10 GMT

Hi,

Why not change the fiscal year to be pervious yr?

=Sum({<FISCALYEAR={"$(=PrYearofToday)"},DateNum={">=$(=Num(FISCALYEARSTART))<=$(=Num(Today()))"}>} Sales)

Regards,

Anagha