topic [ASK] common AsOfDate script issue in QlikView

[ASK] common AsOfDate script issue

Fri, 13 Aug 2010 03:47:45 GMT

hello..

i found an issue about AsOfDate function

i use AsOfDate to compare result between current day volume and yesterday volume

but it can only get the yesterday result, not the day before yesterday

just like the picture below :

A picture i use AsOfDate, but when the date is 09/08/2010 the yesterday volume is 0

the correct one that it must have 06/08/2010 volume

if i use above function, it can't sort by expression

how to make AsOfDate function able to do that?

i mean..i want to AsOfDate able to get the volume just like above function did

i also have attached the file

many thx..

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/8524.tes-compare.rar:550:0]

[ASK] common AsOfDate script issue

tresB — Fri, 13 Aug 2010 05:42:13 GMT

Hi Kribo,

The probelem is with the script. When you write

LOAD

TRANS_SAHAM_DATE AS AsOfDate,

1 AS YesterdayFlag,

Date(TRANS_SAHAM_DATE-1) AS TRANS_SAHAM_DATE

RESIDENT tbl_trans_saham ;

see then what happens for the date 09/08/2010, AsOfDate as 09/08/2010 but the TRANS_SAHAM_DATE is 08/08/2010 which is not there in the AsOfDate. so the YesterdayFlag does not get any value for that date...So 0 is the output. Therefore you have to change the logic in the script i believe.

Thanks and regards,

tresesco

[ASK] common AsOfDate script issue

Fri, 13 Aug 2010 05:45:53 GMT

Hello Tres..

that's the problem, logic of syntax

maybe u have any idea?

i really need an idea for this case

many thx..

[ASK] common AsOfDate script issue

tresB — Fri, 13 Aug 2010 06:00:16 GMT

Hi,

Try this,

LOAD

TRANS_SAHAM_DATE AS AsOfDate,

1 AS YesterdayFlag,

peek(TRANS_SAHAM_DATE) AS TRANS_SAHAM_DATE

RESIDENT tbl_trans_saham;

I think this would help you.

...tresesco

[ASK] common AsOfDate script issue

Fri, 13 Aug 2010 08:04:57 GMT

hello tres..

the peek doesn't work

it give the same number for yesterday

[ASK] common AsOfDate script issue

tresB — Fri, 13 Aug 2010 08:25:18 GMT

This because of renaming.

write it as - peek(TRANS_SAHAM_DATE) as PreTransDate

then recursive reference problem would not be there.

[ASK] common AsOfDate script issue

Fri, 13 Aug 2010 12:47:07 GMT

hello tres..

when i did with this syntax :

peek(TRANS_SAHAM_DATE) as TRANS_SAHAM_DATE

then the result show like pic below :

when i'm using this syntax : peek(TRANS_SAHAM_DATE) as pre_TRANS_SAHAM_DATE

then the result is :

FYI, the yesterday volume syntax is :

=SUM({<Date=,YesterdayFlag={1}>} TRANS_SAHAM_VOLUME)

i'm kinda stuck

😞