Month Order in Cyclic Group

[#1]

I've been searching for how to get my month order in the traditional Jan - Dec format but I can't seem to find anything when it comes to having the month in a cyclic group. I have Yield_Year, Yield_Quarter, Yield_Month, and Yield_Day in the group but when I look at the chart by Yield_Month, it sorts the months by alpha order. Any tips for how to sort this while setting up the bar chart in a cyclic group? Thanks!

[JonnyPoole]

You can set the sort order on each dimension in a cycle (or drill ) group

[Anonymous]

If you go to the Groups tab of the document properties and highlight your cycle group and hit the Edit button, there's an option just above OK for Sort Orders (see screen shot). This lets you define individual sort options for all the components of your cycle group.

[thomaslg_wq]

Hi,

It means your Yield Month field is in "string" format. Not a number format.

You could have a dual format by doing in your script :

Data:

LOAD

Yield_Year,

Yield_Quarter,

// Yield_Month (not used as it's string)

month(Yield_Day) as Yield_Month,

Yield_Day

from/resident source;

But it is assuming you Yield_Day is a date

If you're Yield_Day is the number of the day in the month then, you should rather do :

Map_MonthNumbers:

MAPPING

LOAD * INLINE [

    Yield_Month, MonthNumber

    January, 1

    February, 2

    March, 3

    April, 4

    May, 5

    June, 6

    July, 7

    August, 8

    September, 9

    October, 10

    November, 11

    December, 12

];

Data:

LOAD

Yield_Year,

Yield_Quarter,

// Yield_Month (not used as it's string)

Month(makedate(Yield_Year,applymap('Map_MonthNumbers',Yield_Month,null()),Yield_Day)) as  Yield_Month,

Yield_Day

from/resident source;

[#5]

YES - thank you - this is the problem! My Yield_Month is set up as a varchar and my Yield_Day is every day of that particular month. I'm loading these tables from Microsoft SQL Server Management Studio. Is it best to add a column that lists the month number to correspond to the month name then or what is the best way to approach this?

[thomaslg_wq]

If you have the month number then the simplest approach is load your data as :

LOAD

Yield_Year,

Yield_Quarter,

Yield_Day

Month(makedate(Yield_Year,MONTHNUMBER,Yield_Day)) as  Yield_Month,

makedate(Yield_Year,MONTHNUMBER,Yield_Day))  as Yield_Date,

And others data

from/resident source;

With the month function, your field will be in dual format, it means it will be January/february, etc. but also 1/2,etc. so QlikView will sort almost automatically.

But as always, the BEST approach is to only load:

LOAD

makedate(Yield_Year,MONTHNUMBER,Yield_Day))  as Yield_Date,

And others data

from/resident source;

And then create a master calendar in another table with the link on the Yield_Date:

You should see this :Creating A Master Calendar

[#7]

Thank you - this is seeming to be the right track. The video was really nice too! The place I'm getting stuck is that I'm loading it differently and not sure if the approach you mention (and the video mentions) is going to work... I'm new to this so I'm still getting the hang of everything. I've attached my scripts:

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=C_FLAT;Data Source=PROTOCHIPS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=PROTOCHIPS;Use Encryption for Data=False;Tag with column collation when possible=False];
//-------- Start Multiple Select Statements ------
SQL SELECT *
FROM "C_FLAT".dbo."ACCOUNT_DIM";

SQL SELECT *
FROM "C_FLAT".dbo."OPPORTUNITY_DIM";

SQL SELECT *
FROM "C_FLAT".dbo."PARTS_DIM";

SQL SELECT *
FROM "C_FLAT".dbo."SALES_FACT";

SQL SELECT *
FROM "C_FLAT".dbo."SALES_FACT";

SQL SELECT *
FROM "C_FLAT".dbo."SALES_TIME_DIM";

SQL SELECT *
FROM "C_FLAT".dbo."YIELD_FACT";

SQL SELECT *
FROM "C_FLAT".dbo."YIELD_TIME_DIM";
//-------- End Multiple Select Statements ------

[arasantorule]

Hi,

Use the below expression for sorting Month-Year when Alphabetical sorting causes a problem. Change the date format as per your requirement.

month(date#(Month1,'MMM-YYYY'))&Year(date#(Month1,'MMM-YYYY'))

Hope this helps.

Thanks

[thomaslg_wq]

Hi,

To help you, we would need the real fields loaded, and not the star load *.

Or, show us a print screen of your data model.