Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
can you post a sample file here?
I have attached the screenshots of the same
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.
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
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)
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;