Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sorting problem in drillgroup

I am creating a bar chart that drilling from year to month.for year it is coming in sequence from 2007 to 2010. But for month it is coming in alphabetical order . How to resolve this issue. and secondly i want to include quarter in between year and month.so how to do the same

6 Replies
Not applicable
Author

can you post a sample file here?

Not applicable
Author

I have attached the screenshots of the same

Not applicable
Author

You can use Month num in the sort order (see the attached file).

To add quarter in the drill down group, go to Document properties->Groups. There you can edit your Drill down group.

Not applicable
Author

I cannot open ur file because i am using personal edition can u plz provide screen shots. n proper steps to do the same. Ur help would be highly appreciated

Not applicable
Author

Hi,

First create a mapping table to get the Month Number of each Month

Ex:

Month_No:
Mapping load * inline
[
Month_Name, Month_No
Jan, 1
Feb, 2
Mar, 3
Apr, 4
May, 5
Jun, 6
Jul, 7
Aug, 8
Sep, 9
Oct, 10
Nov, 11
Dec, 12
];

Date_Dim:
LOAD Date_id,
Month_Name,
applymap('Month_No',Month_Name) as Month_No,
Year
FROM
Sales.xls
(biff, embedded labels, table is Date_Dim$);

Then use the Month_No to sort the Monthname. To do this go to Document properties--> Groups. (See the attached file)

Not applicable
Author

Hi,

You must create the month from a valid date, this is not necessary to use map table.

Use the Function Month.


Calendar:
Load
Date,
Year(Date) AS Year,
'Q' & ceil(month(Date)/3) AS Quarter,
Month(Date) AS Month,
MonthName(Date) AS MonthName,
Week(Date) AS Week,
WeekName(Date) AS WeekName,
Day(Date) AS Day,
WeekDay(Date) AS WeekDay;
Load Distinct Date
From FactTable;