topic Re: Date format in QlikView

Date format

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Date format

tresB — Tue, 17 Jul 2018 08:44:05 GMT

Try like:

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

Re: Date format

arvind_patil — Tue, 17 Jul 2018 08:46:05 GMT

Hi nareshthavidishetty,

Try below logic,

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

Thanks & Regards,

Arvind Patil

Re: Date format

nareshthavidishetty — Tue, 17 Jul 2018 08:51:42 GMT

Hi,

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

Thanks..

Re: Date format

nareshthavidishetty — Tue, 17 Jul 2018 08:52:29 GMT

Hi,

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

Thanks..

Re: Date format

qlikviewwizard — Tue, 17 Jul 2018 08:58:17 GMT

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

];

Re: Date format

nareshthavidishetty — Tue, 17 Jul 2018 09:05:29 GMT

ohh My bad.

Yes its working.

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

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

Re: Date format

tresB — Tue, 17 Jul 2018 09:10:50 GMT

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