Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
(Qlik Sense)
I have a date field named "ASDT" and multiple dates are stored in this dimension.
I want to pick the last month-end date and store it in a variable.
For example, today's date is 19-10-2021, but the variable should store the date as 30-09-2021.
Please help me.
Hi @risabhroy_03 ,
Can you please try this
MonthEnd(MonthStart(now())-1)
Hope this works for you.
Hi @risabhroy_03 ,
Can you please try this
MonthEnd(MonthStart(now())-1)
Hope this works for you.
And how to store it in a variable?
You can store the same expression in the variable
and used in the table as =$(LastMonth)
But how to implement this for my dimension "ASDT"?
Because you gave a generic answer, what if ASDT = 03/04/21 and I want my variable to store 31/03/2021?
Do you want to mean in set expression?
If yes can please share the expression?
No,
I have to load multiple dimensions from my table and I want to load all these dimensions based on a given condition and the condition is
All my dimensions should be based on (ASDT), where ASDT is the As on date
and ASDT should have last date of month.
My idea is to add a WHERE condition at the end of the table which will just load the data of the end date of that particular month.
Hi @risabhroy_03 ,
I am assuming that last month would be calculated on the current date.
Try something like below
let Vlastmonthdate=MonthEnd(MonthStart(now())-1);
LOAD
ProductID,
Country as CName,
"Promotion Start",
"Promotion End"
FROM [lib://test/Dump.xlsx]
(ooxml, embedded labels, table is PramotionDetail)
where "Promotion End"=$(Vlastmonthdate);
Please ignore the table fields names
Ok I will try and update.