topic Re: Convert Dates into month and year in QlikView

Convert Dates into month and year

saumyashah90 — Tue, 22 Sep 2015 07:55:06 GMT

Hi friends,

I have a Date Column.

trade_date
2015-03-01
2015-03-07
2015-04-06
2015-04-02
2015-04-01
2014-12-23
2014-12-14

I want out put as

output
Mar 2015
Apr 2015
Dec 2014

When I select Mar 2015, data of all dates of march should be selected.

Help

Re: Convert Dates into month and year

PrashantSangle — Tue, 22 Sep 2015 07:57:56 GMT

Hi,

use Monthname()

date(datefield,'MMM YYYY')

Regards

Re: Convert Dates into month and year

Anonymous — Tue, 22 Sep 2015 08:12:53 GMT

First you need to check if your dates are in date format or not.

if they are in date format and you need to include one more column in script

load

Trade_date,

MonthName(Trade_date) as MonthYear

from table;

Now you can use this field as list box for filtering the data. And when you will select one month it will select all the dates for that particular month Year .

Thanks

BKC

Re: Convert Dates into month and year

Tue, 22 Sep 2015 08:19:18 GMT

Hi Saumya,

is trade_date a proper date or a string?

If it's not a proper date you'll need to use date#

Date(Date#(trade_date, 'YYYY-MM-DD'),'MMM YYYY')

hope that helps

Joe

Re: Convert Dates into month and year

rajkumarb — Tue, 22 Sep 2015 08:19:45 GMT

Try this in the List Box by choosing expression and use this

=Date( MonthStart(Trade_date), 'YYYY-MMM')

or as Max dreamer & Balakumar mentioned use MonthName(Trade_date)

Re: Convert Dates into month and year

sasiparupudi1 — Tue, 22 Sep 2015 08:58:35 GMT

Add another field in your load script

load

trade_date,

Date(Date#(trade_date, 'YYYY-MM-DD'),'MMM YYYY') as [trade date MMM YYYY],

...

from your data source;

hth

Sasi

Re: Convert Dates into month and year

ahmar811 — Tue, 22 Sep 2015 09:02:43 GMT

Date(Date#(trade_date,'YYYY-MM-DD'),'MMM YYYY')

Re: Convert Dates into month and year

MarcoWedel — Tue, 22 Sep 2015 23:26:56 GMT

Hi,

one solution to create both date and month could be:

LOAD *,
        MonthName(trade_date) as trade_month;
LOAD Date#(trade_date,'YYYY-MM-DD') as trade_date
FROM [https://community.qlik.com/thread/181791] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco