Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Group Date Field Into Months

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.

1 Solution

Accepted Solutions

Re: Group Date Field Into Months

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

4 Replies
MVP & Luminary
MVP & Luminary

Re: Group Date Field Into Months

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
Honored Contributor

Re: Group Date Field Into Months

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

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

manucamon
Valued Contributor III

Re: Group Date Field Into Months

HI Roy, you can use MonthName function:

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

Regards!