topic Leap Year Help in QlikView

Leap Year Help

Thu, 03 Mar 2016 13:59:35 GMT

Hi,

**I think my issue is to do with (vSystemDate)='01/03/2016' and (vSystemDate)>'01/03/2016' not working correct;y. I am not sure it see it as a date as when i do this let vtest2= if($(vSystemDate)>='01/03/2016',0,1 the results comes back as 1 even though todays date is after this date....HELP?

I am having issues with 2016 as it's a leap year.

I am using the following to help match day on day for last year. the variable we use to find last years figure is vPriorReportingDate.

This is how we use it.

if(GetSelectedCount(CalendarDate)= 0 and GetSelectedCount(YearNxWeek)= 0 ,

sum({<CalendarDate={">=$(vSystemPriorWeekStartDate) <=$(vPriorWeekEndDate)"}, TransactionDate = {'<=$(=(vPriorReportingDate))'}, CalendarYear = >}REV)

sum({<CalendarDate={"$(=concat(distinct DateCompPriorYear,'","'))"},YearNxWeek={">=$(vSystemPriorYearWeek) <$(vPriorWeekEnd)"},TransactionDate = {'<=$(=(vPriorReportingDate))'}>}REV)

)

This is what it was:

let vPriorReportingDate = date((AddMonths((today()-1),-12)+1),'DD/MM/YYYY');

but because of the leap year monday didn't map to monday so what i need to do is the following for vPriorReportingDate:

let vSystemDate = date((today()),'DD/MM/YYYY');

let vPriorReportingDate = if($(vSystemDate)='01/03/2016',date((AddMonths((today()-2),-12)+2),'DD/MM/YYYY'),

if($(vSystemDate)>'01/03/2016',date((AddMonths((today()-1),-12)+2),'DD/MM/YYYY'),date((AddMonths((today()-1),-12)+1),'DD/MM/YYYY')));

In the image below yellow means 2 and green means 1

Please can anyone help.

Re: Leap Year Help

sunny_talwar — Thu, 03 Mar 2016 14:14:18 GMT

Wow this seems like some complicated stuff. I don't even know what needs to be resolved. Would you be able to share a sample so that we can relate the description to some real stuff

Re: Leap Year Help

Thu, 03 Mar 2016 14:16:48 GMT

Hi sunny

thanks for getting back to me on this.

i think my main issue is that its not reading my dates properly when im trying to set the variable for example:

let vSystemDate = date((today()),'DD/MM/YYYY');

this equals 03/03/2016

however when i use this let vtest2= if($(vSystemDate)>='01/03/2016',0,1)

vtest2 comes back as 1, shoudl come back as 0 as its TRUE

Happy to share if you cant see any issue with this.

Re: Leap Year Help

marcus_sommer — Thu, 03 Mar 2016 14:22:15 GMT

Like Sunny said, it's difficult to see where you have which problem. By your first comparing you need single-quotes around the variable, like: if('$(vSystemDate)'>='01/03/2016',0,1) then your date is a string in this case.

For your leap-year topic and the getting the same workday from the previous day, see here: Re: date of last year same day.

- Marcus

Re: Leap Year Help

sunny_talwar — Thu, 03 Mar 2016 14:55:20 GMT

If vSystemData is 03/03/2016, why would it equal 01/03/2016 and give 0 (true)?