Custom Sort in a Chart

[#1]

Hi,

Is it possible to perform a custom sort in a chart?

I have a dimension which shows the Month and the Year - e.g. May 2012 (these appear on the x-axis of the graph)

Is it possible to display these in calendar order?  E.g.  Mar 2012, Apr 2012 and May 2012?  At the moment, they are appearing in alphabetical order.

The options I have are only:

• Y-Value
• State
• Expression
• Frequency
• Numeric Value
• Text
• Load Order

Any help would be greatly appreciated.

Thanks.

[#2 · Accepted solution]

hi,

You can sort this by using Dual Function in backend.

Change the date field in num format and name it as Example "DateNum".Suppose ur dimension field having values as Mar 2012, Apr 2012 is "[Month Year]".

thn u have to write Dual([Month Year],DateNum). This will sort ur [Month Year] field.

[jagannalla]

Hello,

I've worked with sample data. i.e.

Main:

LOAD MonthName(F1) as Year,F2 as Value;

LOAD * INLINE [

    F1, F2

    04-02-2012, 4

    03-01-2012, 3

    05-03-2012, 5

];

- Take dim as Year and exp as Count of value.

- Sort tab -> Check Y-Value and give ascending order (or) check exp and give exp as =MonthName(Year) select ascending order.

Cheers!

Hope it helps you.

[#4]

Hi Pratibha,

How do I go about changing the date field to num format within the script?

Num(Date#([Month Year], 'MMM YYYY')) AS NumericDate

[#5]

HI,

Dont change [Month Year] to num.

u must be having a date field in your data model having value like 1/1/2012,1/2/2012 ,1/3/2012..............

that particular date field is required to be changed in number format so that that number sequence can be used by dual function to sequence your [Month Year] field

suppose ur date field name in data model is "Date1"

you can change ur date field i.e "Date1" to number format by

      num(Date1)  as DATE

now write

    Dual([Month Year], DATE) as [Month Year]

[jagannalla]

Hello,

Just assume that F1 column is date field in u r data. Now design your chart according to my suggestions. For your reference i'm attaching my file. Otherwise please upload your sample file.

Cheers!

Hope it helps you

[#7]

Hi Pratibha,

There are no date fields in my data.  I am extracting the Month/Year from the Filename.

The filename is structured as: "Data - Apr 2012.xls"

So in my script, I am extracting the "Apr 2012". 

But the dual method works now and it is now sorting it in the correct order.

Many thanks for your help!