Last Month end date

[risabhroy_03]

(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.

[abhijitnalekar · Accepted solution]

Hi @risabhroy_03 ,

Can you please try this

MonthEnd(MonthStart(now())-1)

Hope this works for you.

[risabhroy_03]

And how to store it in a variable?

[abhijitnalekar]

You can store the same expression in the variable

and used in the table  as =$(LastMonth)

[risabhroy_03]

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?

[abhijitnalekar]

Do you want to mean in set expression?

If yes can please share the expression? 

[risabhroy_03]

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.

[risabhroy_03]

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.

[abhijitnalekar]

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

[risabhroy_03]

Ok I will try and update.