need help in understanding some fields from calendar

[fashid]

Hi,

Can anybody explain me these two fields

           YeartoDate(TempDate)*-1 as CurYTDFlag,

           YeartoDate(TempDate,-1)*-1 as LastYTDFlag

           Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,

           Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag,

why are we doing -1 in this fields and how can i use these two fields in my application.

The last two fields are from a different master calendar.

Thanks & Regards,

Nadeem

[its_anandrjs]

Hi,

1. YeartoDate(TempDate)*-1 as CurYTDFlag

this function returns -1 for true and false 0 and when you do *-1 the value you get is 1 or 0

2. YeartoDate(TempDate,-1)*-1 as LastYTDFlag

This also returns -1 or 0 same here for getting 0 and 1 you multiply -1 to get 1 or 0 for last year but using YeartoDate(TempDate,-1) it will return last year value

3. And this Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag functions check the value if it exists in the given date or not if exists then returns -1 else 0

4. Same for the Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag

Regards

Anand

[fashid]

hi,

Thanks for replying . I got that part of multiplying (-1) , but i also wanted to know what is the use of these functions 

regards,

nadeem

[Anonymous]

If you read the help files this is explained quite well. (YearToDate and Year2Date could replace each other)

"YearToDate(date [ , yearoffset [ , firstmonth [ , todaydate] ] ])

Returns true if a date falls within the year to date, else false. If none of the optional parameters are used, the year to date means any date within one calender year from January 1 up to and including the date of the last script execution.

By specifying a yearoffset (0 if omitted), the function can be transposed to return true for the same period in another year. A negative yearoffset indicate previous years while a positive one indicates coming years. Last year to date is achieved by specifying yearoffset = -1. "

The general concept of year to date is to sum upp all (e.g.) sales from the 1/1 to the current date and in your example compare it to the previous year. Most likely you apply the flags to something like =SUM(Sales*CurYTDFlag) or alternatively they are used in a set analysis like =SUM({<CurYTDFlag={1}>} Sales).

Kind regards

Niklas

[its_anandrjs]

Hi,

From Qlik Help

YearToDate(date [ , yearoffset [ , firstmonth [ , todaydate] ] ])

Returns true if a date falls within the year to date, else false. If none of the optional parameters are used, the year to date means any date within one calender year from January 1 up to and including the date of the last script execution.

By specifying a yearoffset (0 if omitted), the function can be transposed to return true for the same period in another year. A negative yearoffset indicate previous years while a positive one indicates coming years. Last year to date is achieved by specifying yearoffset = -1.

By specifying a firstmonth between 1 and 12 (1 if omitted) the beginning of the year may be moved forward to the first day of any month. If you e.g. want to work with a fiscal year beginning on May 1, specify firstmonth = 5.

By specifying a todaydate (timestamp of the last script execution if omitted) it is possible to move the day used as the upper boundary of the period.

Examples:

Assume last reload time = 1999-11-18

yeartodate( '1998-11-18') returns false

yeartodate( '1999-02-01') returns true

yeartodate( '1998-11-18', -1) returns true

yeartodate( '1999-11-18', -1) returns false

yeartodate( '1999-04-30', 0, 5) returns false

yeartodate( '1999-05-01', 0, 5) returns true

This flags are used to filter the rows or the data that you have in the model and for what ever reason that is used

1. For viewing the last or current year data set

2. If want to use in the Aggregation function (Sum, Avg, Count, Only) for sum particular data set.

3. Or if it is used in the SET expression this are the reasons.

Hope this helps

Regards

Anand

[fashid]

Hi Anand ,

"By specifying a yearoffset (0 if omitted), the function can be transposed to return true for the same period in another year .

A negative yearoffset indicate previous years while a positive one indicates coming years. Last year to date is achieved by specifying yearoffset = -1."

so if I write yeartodate( '1998-11-18', -1)  , it will just check whether the date falls between  1998-01-01 and the last reload time = 1999-11-18 , if the date falls between these dates it will return -1 else 0 right .

Correct me if i am wrong .

Sorry if this question seems stupid but it has been troubling me for a while .

Regards,

Nadeem

[Anonymous · Accepted solution]

Check the following three examples:

YearToDate('1998-11-10', 0,1,'1998-11-18')  will result in -1 (true)

This is true as the date 1998-11-10 can be found between 1998-01-01 and 1998-11-18

YearToDate('1998-11-10', -1,1,'1998-11-18') will result in 0 (false)

This is false as the date 1998-11-10 cannot be found between 1997-01-01 and 1997-11-18

YearToDate('1997-11-10', -1,1,'1998-11-18') will result in -1 (true)

This is true as the date 1997-11-10 can be found between 1997-01-01 and 1997-11-18. Do notice that we have offset the years with -1 (year).

The first date parameter is the one you compare to the date range. The second (last) date parameter can either be set manually, as in the example above, or will default to the last reload date.

Kind regards

Niklas

[fashid]

Hi Niklas,

Thank you so very much it is now very much clear to me  the way you have explained with the examples..

Thanks once again and have a great day !!!

Thanks & Regards,

Nadeem