Discussion board where members can get started with Qlik Sense.
I have created a variable in script. And I'm trying to use that variable in a where condition. I'm unable to get the result value from that variable.
let vd = 'max("Business Date")';
load "Business Date",Sum(Usage)
where "Business Date" = '$(vd)'
group by "Business Date";
I tried $(vd) and also '$(vd)'. Both are not working. I couldnt get the max date assigned in where condition.
Kindly provide me correct syntax.
You have to pass through an intermediate table:
TempTable: Load Max(BusinessDate) as BusinessDate Resident SourceTable: Let vd=peek('BusinessDate',0,'TempTable'); Drop table TempTable check: Load .... .... .... Resident .... where BusinessDate='$(vd)' group by ...;
Qlik doew not know the max([Business Date]) in when used in the where. You will need another approach. You could try to figure out the maxvalue in advance so that vd contains the value and not the formula (Edit: I notice that @micheledenardi posted such a solution before I posted this answer). Or you could do an right join on it self using max(Business date) like I've done below.
SET vd = max("Business Date"); check: load "Business Date", Sum(Usage) as Usage inline [ Business Date, Usage 2019-01-01, 100 2019-01-01, 100 2019-02-01, 200 2019-02-01, 200] group by "Business Date"; Right JOIN LOAD $(vd) as [Business Date] RESIDENT check;
(Replace my inline with your own source data)
I'm getting below error when changed the script as you mentioned:
Thanks for your script . It is working fine.
But, I have one more requirement:
max("Business date") is taking the max date. But, I want it to change as per user selection.
Like, If I have dates from 1/1/2019 to 1/30/2019.
By default, max("Business date") will take 1/30/2019.
If I select 1/10/2019 in filter, then I want max(Business date) to be changed to 1/10/2019.
Can you help me in getting this?
Try this, i've added a date() formatting during the valorization of the variable:
TempTable: Load Max(BusinessDate) as BusinessDate Resident Posting; Let vd=date(peek('BusinessDate',0,'TempTable'),'DD/MM/YYYY'); Drop table TempTable; check: Load .... .... .... Resident .... where BusinessDate='$(vd)' group by ...;