topic Re: Date Format & Merging Columns in QlikView

Date Format & Merging Columns

Wed, 05 Sep 2012 06:01:31 GMT

How do i change a date format from say, Jan 2012 to Jan-12
How do i merge columns. I have the table below with each column showing Forecast, however i want to have a main heading called "Forecast" and the months below.
Forecast - Jan 2012 Forecast - Feb 2012 Forecast - March 2012

kind regards

Nayan

Re: Date Format & Merging Columns

thomas_skariah — Wed, 05 Sep 2012 06:30:55 GMT

Hi Nayan,

You can try the following code.

='Forecast-' & Date#(date(YourDateField,'MMM-YY'))

Regards,

Tom

Re: Date Format & Merging Columns

Wed, 05 Sep 2012 07:02:46 GMT

Hi Thomas

Thank you for your feedback. . I've used your formula, but it did not work. Im new at Qlikview, and below the table is the scripting for the columns that was created for us.

Forecast Sales Qty Oct-2012

Forecast Sales Qty Nov-2012

Forecast Sales Qty Dec-2012

='Forecast Sales Qty ' & month(addmonths(max([Trans Date]) ,1)) & '-' & year(addmonths(max([Trans Date]) ,1))

='Forecast Sales Qty ' & month(addmonths(max([Trans Date]) ,2)) & '-' & year(addmonths(max([Trans Date]) ,2))

='Forecast Sales Qty ' & month(addmonths(max([Trans Date]) ,3)) & '-' & year(addmonths(max([Trans Date]) ,3))

kind regards

Nayan

Re: Date Format & Merging Columns

thomas_skariah — Wed, 05 Sep 2012 07:20:07 GMT

Hi Nayan,

You can try this one

='Forecast Sales Qty ' & month(addmonths(max([Trans Date]) ,1)) & '-' &date(date#(max(Year([Trans Date])), 'YYYY'),'YY').

Regards,

Tom

Re: Date Format & Merging Columns

Wed, 05 Sep 2012 10:23:28 GMT

Hi Tom

Apologies for the dealyed reply. The result is as follows where there year has fallen away:

Forecast Sales Qty Oct-

Re: Date Format & Merging Columns

jonathandienst — Wed, 05 Sep 2012 10:56:37 GMT

These are the expressions you need

='Forecast Sales Qty ' & Date(addmonths(max([Trans Date]), 1), 'MMM-YY')

='Forecast Sales Qty ' & Date(addmonths(max([Trans Date]), 2), 'MMM-YY')

='Forecast Sales Qty ' & Date(addmonths(max([Trans Date]), 3), 'MMM-YY')

Regards

Jonathan

Re: Date Format & Merging Columns

Wed, 05 Sep 2012 11:00:44 GMT

Hi Jonathan

Thank you for you help. It worked. On my 2nd query on how to merge columns, is there a way this can be done?

kind regards

Nayan

Forecast - Jan 2012	Forecast - Feb 2012	Forecast - March 2012