Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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;
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
HI Roy, you can use MonthName function:
Monthname('23/05/2016') -> May 2016
Regards!