Date format

[nareshthavidishetty]

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

[tresesco]

Try like:

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

[arvind_patil]

Hi nareshthavidishetty,

Try below logic,

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

Thanks & Regards,

Arvind Patil

[nareshthavidishetty]

Hi,

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

Thanks..

[nareshthavidishetty]

Hi,

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

Thanks..

[qlikviewwizard]

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

];

[nareshthavidishetty]

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

[tresesco · Accepted solution]

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%')