topic Re: need help in understanding some fields from calendar in QlikView

need help in understanding some fields from calendar

fashid — Mon, 08 Dec 2014 06:42:25 GMT

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

Re: need help in understanding some fields from calendar

its_anandrjs — Mon, 08 Dec 2014 06:58:22 GMT

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

Re: need help in understanding some fields from calendar

fashid — Mon, 08 Dec 2014 07:53:47 GMT

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

Re: need help in understanding some fields from calendar

Anonymous — Mon, 08 Dec 2014 08:11:30 GMT

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

Re: need help in understanding some fields from calendar

its_anandrjs — Mon, 08 Dec 2014 08:20:18 GMT

Hi,

From Qlik Help

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

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

Re: need help in understanding some fields from calendar

fashid — Mon, 08 Dec 2014 09:55:55 GMT

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

Re: need help in understanding some fields from calendar

Anonymous — Mon, 08 Dec 2014 12:37:58 GMT

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

Re: need help in understanding some fields from calendar

fashid — Tue, 09 Dec 2014 05:35:33 GMT

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