Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

convert 18/10/2015 to 10/2015 or OCT/2015

hi all,

I have a list of date such as 18/10/2015 and I want to convert them to:

1. 10/2015

2. Oct/2015

can advise, pls?

8 Replies
sunny_talwar

Try this:

For 10/2015

Date(Date#(DateField, 'MM/DD/YYYY'), 'MM/YYYY') as NewDateField

Date(MonthStart(Date#(DateField, 'MM/DD/YYYY')), 'MM/YYYY') as NewDateField

For Oct/2015

Date(Date#(DateField, 'MM/DD/YYYY'), 'MMM/YYYY') as NewDateField

Date(MonthStart(Date#(DateField, 'MM/DD/YYYY')), 'MMM/YYYY') as NewDateField

Update: updating my expression because I think Peter is right that we need to use MonthStart or MonthEnd so that we don't see repeating MonthYear on the front end because of the date information that is still intact if we don't use them.

Peter_Cammaert
Partner - Champion III
Partner - Champion III

If you plan to use this new field as a dimension to group facts, you may want to use MonthStart().

If OTOH you use simple Date format manipulation, your date values will keep the different days in their underlying numerical representation. A result of which could be that displaying a listbox with values from the new field may list '10/2015' multiple times because they're still different dates. Which is maybe not what you want.

If your default DateFormat string is already set at 'D/MM/YYYY', you can use:

Date(MonthStart(CurrentDate), 'MM/YYYY') AS NewDate // for 10/2015

or

Date(MonthStart(CurrentDate), 'MMM/YYYY') AS NewDate // for Oct/2015

Note that each part before the AS keyword can also be used as an expression in the User Interface, or as a listbox expression.

Best,

Peter

Kushal_Chawda

=date(Date#(DateField,'DD/MM/YYYY'),'MMM/YYYY')

or

date(Date#(DateField,'DD/MM/YYYY'),'MM/YYYY')

Chanty4u
MVP
MVP

try this


max-date.png=date(date#(urfield,'MM/DD/YYYY'),'MMM/YYYY') as newdate

khadeer
Specialist
Specialist

Hi

     1. Date(Date#(18/10/2015,'DD/MM/YYYY'),'MM/YYYY')

     2. Date(Date#(18/10/2015,'DD/MM/YYYY'),'MMM/YYYY')

Regards,

Khadeer

Peter_Cammaert
Partner - Champion III
Partner - Champion III

To make this work, you may need some additional quoting. Otherwise the calculation 18 / 10 / 2015 will become a very small number instead of a date string 

rgvavihs
Specialist
Specialist

=Date(YourDateField,'MMM/YYYY')

in the main tab 

set MonthNames :Jan,Feb,......Dec;

ahammadshafi
Creator
Creator

For 10/2015

Date(MonthStart(Date#(DateField, 'MM/DD/YYYY')), 'MM/YYYY') AS [NewDate]

For Oct/2015

Date(MonthStart(Date#(DateField, 'MM/DD/YYYY')), 'MMM/YYYY') AS [NewDate1]