topic Re: Group Date Field Into Months in QlikView

Group Date Field Into Months

Wed, 22 Jun 2016 20:55:08 GMT

Hi,

I am using Qlik Desktop

I have a field in my database that is formatted this way: MM/DD/YYYY

For example:

01/04/2016

01/05/2016

01/06/2016

...

05/05/2016

Is there a possible way to group everything in months so it will show on my PivotTable

January 2016

February 2016

March 2016

And under the month will be:

January 2016

01/04/2016

01/05/2016

01/06/2016

February 2016

02/01/2016

02/02/2016

Thank you very much.

Re: Group Date Field Into Months

marcus_sommer — Wed, 22 Jun 2016 21:07:48 GMT

I think you should use a master-calendar: How to use - Master-Calendar and Date-Values which would give you all needed period-fields and a view similar to your requirement could you get if you the used a pivot-chart with MonthYear and Date as Dimensions.

- Marcus

Re: Group Date Field Into Months

trdandamudi — Thu, 23 Jun 2016 00:22:08 GMT

May be something as attached:

Source_Data:

Load * Inline [

SalesDate,SalesVolume

01/01/2016,1000

01/02/2016,3000

03/30/2016,2500

03/31/2016,3100

04/8/2016,6238

04/10/2016,4816

05/05/2016,7522

];

NoConcatenate

DailySales:

Load date(Date#(SalesDate,'MM/DD/YYYY')) as SalesDate,

SalesVolume

Resident Source_Data;

CalendarMaster:

LOAD

Date(SalesDate) AS SalesDate,

Year(SalesDate) AS Year,

Month(SalesDate)&'-' &Year(SalesDate) AS MonthYear,

'Q' & Ceil(Month(SalesDate) /3) AS Quarter,

Month(SalesDate) AS Month,

Day(SalesDate) AS Day,

Week(SalesDate) AS Week;

Load

Date(MinDate + Iterno() -1) AS SalesDate

While (MinDate + Iterno() -1) <= Num(MaxDate);

Load

Min(Date(SalesDate, 'MM/DD/YYYY')) AS MinDate,

Max(Date(SalesDate, 'MM/DD/YYYY')) AS MaxDate

Resident DailySales;

Drop Table Source_Data;

Re: Group Date Field Into Months

sunny_talwar — Thu, 23 Jun 2016 04:08:37 GMT

You can either use MonthName() function: monthname ‒ QlikView‌. But this will not give you the full month name (unless you have specified full monthnames in the environmental variables in the script.

Alternatively, you can try this: Date(MonthStart(Date), 'MMMM YYYY') as MonthYear

MonthStart() is key here which will floor all your dates to 1st of the month and will associate all dates in a month to its MonthYear field.

HTH

Best,

Sunny

Re: Group Date Field Into Months

Anonymous — Thu, 23 Jun 2016 06:31:53 GMT

HI Roy, you can use MonthName function:

Monthname('23/05/2016') -> May 2016

Regards!