Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Rich5678
Contributor III
Contributor III

Date Calculation - Need to calculate 1 day less

Hi,

I have a date field called AS_OF_DATE where data is stored in M/D/YYYY format.

Currently, this field has dates such as 

2/1/2022

3/1/2022

4/1/2022

However, I require Month-end dates such as

1/31/2022

2/28/2022

3/31/2022

I tried the conversion, using following formula

Date(Date#([AS_OF_DATE],'M/D/YYYY')-1,'M/D/YYYY')

However, even when I do this, I still get the same dates as original records.

Can someone help

 

Labels (3)
3 Replies
Digvijay_Singh

Monthend([AS_OF_DATE],-1) should do the needful. You may use Date() around that in case you need formatting

MayilVahanan

Hi

Try like

Date(Floor(MonthEnd(AS_OF_DATE)), 'M/DD/YYYY')

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
vinieme12
Champion III
Champion III

As below

MonthEnd(Date#(FieldName,'MM/DD/YYYY'),-1) as MonthEndDate

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.