Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
See why IDC MarketScape names Qlik a 2025 Leader! Read more
cancel
Showing results for 
Search instead for 
Did you mean: 
carolin01
Partner - Creator II
Partner - Creator II

resident load & where statement

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:

screenshot.png

I would appreciate your comments and ideas.

Best regards

Carolin

1 Solution

Accepted Solutions
Not applicable

Try this ..

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

Regards,

Prabhu

View solution in original post

4 Replies
Not applicable

Try this ..

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

Regards,

Prabhu

qlikpahadi07
Specialist
Specialist

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
Partner - Champion III
Partner - Champion III

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
Partner - Creator II
Partner - Creator II
Author

Perfect - thanks to everybody for your support