resident load & where statement

[carolin01]

Hello,

I´ve the following issue:

I´ve loaded a table from an excel spreadsheet showing each dates and assigning our coporate fiscal year:

CorporateCalendar:

LOAD 

date(floor(Date)) as OverallTransactionDate,
     FWeekday,
     FWeeksPerPeriod,
     FWeek,
     FPeriod,
  date#(FPeriod & FYear,'MM.YYYY') as Datetmp,
     FPeriod & FYear as FYP,
     Fquarter,
     FYear
FROM
$(vDataPath)\Master Fiscal Calendar.xlsx
(ooxml, embedded labels, table is Sheet1);

Now I would like to create a variable that always shows the current fiscal year, e.g. our fiscal year is starting in October so that already in October the variable needs to show the fiscal year 2014. Therefore I tried to create the following:

1. variable showing the date of today - this date I would like to map with the current fiscal year from the table CorporateCalendar

vtoday = Date (floor(Today ()));

2. I created a resident load and wanted to restrict the load only to the variable vtoday but this part is not working. My idea is that later I could substract e.g. -1 from the FYear to also get the previous year in chart formulas.
Todaytmp:
LOAD
     OverallTransactionDate as DateToday,
     FYear
Resident CorporateCalendar
where (DateToday=$(vtoday));

Screenshot from the Debugger:

I would appreciate your comments and ideas.

Best regards

Carolin

[#2 · Accepted solution]

Try this ..

where (OverallTransactionDate='$(vtoday)');

Regards,

Prabhu

[qlikpahadi07]

Hi Carolin,

you need to change your syntax from

Todaytmp:
LOAD
     OverallTransactionDate as DateToday,
     FYear
Resident CorporateCalendar
where (DateToday=$(vtoday));

to

Todaytmp:
LOAD
     OverallTransactionDate as DateToday,
     FYear
Resident CorporateCalendar
where (OverallTransactionDate =$(vtoday));

Actually Qlikview will never find the column DateToday as it is renamed so you have to use orignal column name which is OverallTransactionDate .

hope this works

cheers!!!

[jagan]

Hi,

Give single quotes for the variable, since date is should be given as string.

odaytmp:
LOAD
     OverallTransactionDate as DateToday,
     FYear
Resident CorporateCalendar
where (DateToday='$(vtoday)');

HOpe this helps you.

Regards,

Jagan.

[carolin01]

Perfect - thanks to everybody for your support