topic Sorting problem in drillgroup in QlikView

Sorting problem in drillgroup

Thu, 03 Feb 2011 07:22:30 GMT

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

Sorting problem in drillgroup

Thu, 03 Feb 2011 07:30:41 GMT

can you post a sample file here?

Sorting problem in drillgroup

Thu, 03 Feb 2011 07:40:17 GMT

I have attached the screenshots of the same

Sorting problem in drillgroup

Thu, 03 Feb 2011 08:47:20 GMT

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.

Sorting problem in drillgroup

Thu, 03 Feb 2011 09:44:50 GMT

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

Sorting problem in drillgroup

Thu, 03 Feb 2011 10:09:27 GMT

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)

Sorting problem in drillgroup

Thu, 03 Feb 2011 17:45:21 GMT

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;