Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
nareshthavidish
Contributor III

Date format

Hi,

I have used the below command to extract the monthend date for date selected.

Logic:  =AddMonths(monthend(max(([Calendar Date]),'DD-MMM-YYYY')),-1)

I'm getting the date like '10-02-1987'

Required format:

But my [Calendar Date] format is like '10-Feb-1987'


Please let me know how to convert the date format.


Thanks..

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Date format

Try:

Sum({$<[Month Abbr]=, Year=, [Quarter Name]=,[Calendar Date]={'>$(=Date(AddMonths(max([Calendar Date]),-1)),'DD-MMM-YYYY') <=$(=Date(max([Calendar Date]),'DD-MMM-YYYY'))'}[Conformed Status] -= {'Monitoring*', 'Effectiveness Check'}>}[# SCAR Open]),'#0.0%')

7 Replies
MVP
MVP

Re: Date format

Try like:

Date(monthend(max([Calendar Date]), -1) ,'DD-MMM-YYYY')

Partner
Partner

Re: Date format

Hi nareshthavidishetty,


Try below logic,

Logic:  AddMonths(monthend(date#(max([Calendar Date]),'DD-MMM-YYYY')),-1)


Thanks & Regards,

Arvind Patil

nareshthavidish
Contributor III

Re: Date format

Hi,

I need to use 'Addmonths' for roll back to months.

Thanks..

nareshthavidish
Contributor III

Re: Date format

Hi,

Still getting in 'DD-MM-YYYY' format.

Thanks..

Arjunarao
Honored Contributor II

Re: Date format

Hi,

Try like this.

SET vPrevMonth =DATE(AddMonths(monthend(max([Calendar Date])),-1),'DD-MMM-YYYY');

LOAD DATE#([Calendar Date],'DD-MMM-YYYY') as [Calendar Date],DATE(DATE#([Calendar Date],'DD-MMM-YYYY'),'DD-MMM-YYYY') as NewDate  INLINE [

Calendar Date

10-Feb-1987

10-Mar-1997

10-Apr-2007

10-Feb-2017

10-Dec-1987

];

Capture.PNG

nareshthavidish
Contributor III

Re: Date format

ohh My bad.

Yes its working.

But when i try to replace the logic with Date(monthend(max([Calendar Date]), -1) ,'DD-MMM-YYYY')

in

Sum({$<[Month Abbr]=, Year=, [Quarter Name]=,[Calendar Date]={'>$(=AddMonths(max([Calendar Date]),-1))<=$(=max([Calendar Date]))'}[Conformed Status] -= {'Monitoring*', 'Effectiveness Check'}>}[# SCAR Open]),'#0.0%')

My code is not working.

Thanks..

MVP
MVP

Re: Date format

Try:

Sum({$<[Month Abbr]=, Year=, [Quarter Name]=,[Calendar Date]={'>$(=Date(AddMonths(max([Calendar Date]),-1)),'DD-MMM-YYYY') <=$(=Date(max([Calendar Date]),'DD-MMM-YYYY'))'}[Conformed Status] -= {'Monitoring*', 'Effectiveness Check'}>}[# SCAR Open]),'#0.0%')