Group Date Field Into Months

[#1]

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.

[marcus_sommer]

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

[trdandamudi]

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;

[sunny_talwar · Accepted solution]

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

[Anonymous]

HI Roy, you can use MonthName function:

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

Regards!