topic Re: Custom Sort in a Chart in QlikView

Custom Sort in a Chart

Wed, 20 Jun 2012 09:37:32 GMT

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.

Re: Custom Sort in a Chart

Wed, 20 Jun 2012 09:59:46 GMT

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.

Re: Custom Sort in a Chart

jagannalla — Wed, 20 Jun 2012 10:04:29 GMT

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.

Re: Custom Sort in a Chart

Wed, 20 Jun 2012 10:20:40 GMT

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

Re: Custom Sort in a Chart

Wed, 20 Jun 2012 10:30:58 GMT

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]

Re: Custom Sort in a Chart

jagannalla — Wed, 20 Jun 2012 10:46:15 GMT

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

Re: Custom Sort in a Chart

Wed, 20 Jun 2012 11:09:36 GMT

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!