topic GMT YTD flag-- Load problem in QlikView

GMT YTD flag-- Load problem

karensmith — Tue, 27 Apr 2010 16:05:59 GMT

Does anyone have any sugguestions on how I should handle timezone issues in my scirpt. Any help will be greatly appreciated. I have been struggling with this for sometime now.

Here is my YTD syntax:

Year2Date(INVOICE_DATE,0,4) * -1 as FY_YTD_Flag

My gauge uses :(Sum(SALESVALUE * FY_YTD_Flag))

The problem is when job is executed from publisher at 7:10 edt it does not pickup invoices dated today's date example(4/27/10), but only picks up 4/26/10. On my calendar table YTD Flag is set to '1' through 4/26/10 and does not include 4/27/10. When I select YTD_FLG= 0 then 4/27/10 is included.

For example today job executed @ 7:10 am edt and completed @ 7:20am edt. YTD Flag was off. I manaully executed application again @ 7:56 am edt and job completed @ 8:06am edt..Flags were correct. 4/27/10 was included.

How is YTD determined based on system date and time or current day today() date and time?

What do I need to add to my script to prevent this from happening? Or, do I need to something on the Publisher trigger? I have to load this job before 8:00am edt and include all data upto current day.

Please help!!!!

GMT YTD flag-- Load problem

spsrk_84 — Thu, 29 Apr 2010 07:21:03 GMT

Hi,

As per my understanding if the date lies with in the year2date it returns true i.e -1 in your case but when you are comparing with respect to current day it is showing false i.e 0(Zero), i feel u need to mention one parameter called todayaDate in YEARTODATE FORMAT

i.e

YEARTODATE( DATE [,Yearoffset [ , FirstMonth [ , TodayDate] ] ])

what i mean is give the Date parameter which is the upper bound of the date i.e As shown below

Year2Date(INVOICE_DATE,0,4,Today()) * -1 as FY_YTD_Flag

I tried few samples and it has given the following results

Year2Date( '2010-04-29' ,0,4, '2010-04-26' ) Returns Zero which is 0 i.e False

Year2Date( '2010-04-29' ,0,4, '2010-04-26' ) Returns -1 which is True

Try the above things hope this will reslve the prob;lem,

Regards,

Ajay

GMT YTD flag-- Load problem

karensmith — Thu, 29 Apr 2010 13:48:57 GMT

Ajay,

Thank you for your response. I have added this code to my script. I will see how it runs tommorrow morning when Publisher executes the task.

I greatly appreciate your assistance. I will advise.

Thanks Karen

GMT YTD flag-- Load problem

stephencredmond — Thu, 29 Apr 2010 14:12:21 GMT

Hi,

YearToDate uses the last executed time from the document as its comparison. Your problem is that the last execution time was yesterday at 7am.

So, Ajay's suggestion to use Today() as the date offset would be a good one. Just watch that in his examples he is using "4" as the start month so that depends when your year starts. "4" is April.

Stephen

GMT YTD flag-- Load problem

karensmith — Thu, 29 Apr 2010 14:21:32 GMT

Stephen,

Thank you for your response. I did make the suggested changes, and now I understand that by leaving out that last parameter that causing my issue.

I greatly appreciate your assitance.

Thanks Karen