Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
kaitlynwagner_s
Contributor II
Contributor II

Using Variable to only return data for beginning of week

Hey All!

 

I have a QVD that takes a nightly snapshot of a bunch of data. I would like to start tracking changes over time, however I really only need the first day of the week's snapshot from the QVD. I tried to create a Variable in my Editor to do this for me, but i am getting no results loaded (No errors or anything, just no results). Here is my script:

 

WeekstartPeriod:

LOAD

DISTINCT
weekstart(DataLoadDate,0) as WeekStartDate
FROM [lib://Qlik_Resources/Outstanding_KPI.qvd]
(qvd);

Let WeekStartPeriod = WeekStartDate;

 

LOAD

AccountCode,
AccountName,
Address,
City,
State,
Country,
Telephone,
Email,
InvoicingEntity,
InvoiceCode,
InvoiceNumber,
usrname,
"Comment",
DataLoadDate
FROM [lib://Qlik_Resources/Outstanding_KPI.qvd] (qvd)
where Date(DataLoadDate) = $(WeekStartPeriod)
;

2 Replies
Taoufiq_Zarra

Let WeekStartPeriod = WeekStartDate;

to transfer values into variable we use

 

LET WeekStartPeriod = peek('WeekStartDate');

or

Let WeekStartPeriod = FieldValue('WeekStartDate',1);

but you need to check how many rows you'll get in LOAD DISTINCT weekstart(DataLoadDate,0) as WeekStartDate before you use it.

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Kushal_Chawda

Probably you need this but let me know if my understanding is wrong

WeekstartPeriod:

LOAD
DISTINCT
date(weekstart(max(DataLoadDate),0)) as WeekStartDate
FROM [lib://Qlik_Resources/Outstanding_KPI.qvd]
(qvd);

Let WeekStartPeriod = peek('WeekStartDate',0,'WeekstartPeriod');

 

LOAD
AccountCode,
AccountName,
Address,
City,
State,
Country,
Telephone,
Email,
InvoicingEntity,
InvoiceCode,
InvoiceNumber,
usrname,
"Comment",
DataLoadDate
FROM [lib://Qlik_Resources/Outstanding_KPI.qvd] (qvd)
where Date(DataLoadDate) = '$(WeekStartPeriod)'
;