Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
risabhroy_03
Partner - Creator II

Last Month end date

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

1 Solution

Accepted Solutions
abhijitnalekar
Specialist II

Hi @risabhroy_03 ,

 

Can you please try this

MonthEnd(MonthStart(now())-1)

Hope this works for you.

Regards,
Abhijit
keep Qliking...
Help users find answers! Don't forget to mark a solution that worked for you!

View solution in original post

10 Replies
abhijitnalekar
Specialist II

Hi @risabhroy_03 ,

 

Can you please try this

MonthEnd(MonthStart(now())-1)

Hope this works for you.

Regards,
Abhijit
keep Qliking...
Help users find answers! Don't forget to mark a solution that worked for you!
risabhroy_03
Partner - Creator II
Author

And how to store it in a variable?

abhijitnalekar
Specialist II

You can store the same expression in the variable

and used in the table  as =$(LastMonth)

Regards,
Abhijit
keep Qliking...
Help users find answers! Don't forget to mark a solution that worked for you!
risabhroy_03
Partner - Creator II
Author

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
Specialist II

Do you want to mean in set expression?

If yes can please share the expression? 

Regards,
Abhijit
keep Qliking...
Help users find answers! Don't forget to mark a solution that worked for you!
risabhroy_03
Partner - Creator II
Author

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

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
Specialist II

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

 

Regards,
Abhijit
keep Qliking...
Help users find answers! Don't forget to mark a solution that worked for you!
risabhroy_03
Partner - Creator II
Author

Ok I will try and update.