topic Re: Sorting using an expresion with Set Analysis in QlikView

Sorting using an expresion with Set Analysis

Mon, 29 Oct 2012 15:05:57 GMT

Hi,

I have some data which is stored in by month i.e.

Month	Data
May	100
Apr	150
Aug	200
Jul	20
Jun	50
Dec	80
Oct	120
Jan	130
Mar	125
Sep	180
Nov	160
Feb	90

Because month is a text field i have created an inline table with the following:

DateID	Data
1	Apr
2	May
3	Jun
4	Jul
5	Aug
6	Sep
7	Oct
8	Nov
9	Dec
10	Jan
11	Feb
12	Mar

I have created a graph using set anylsis so that when I select the a date the graph still displays all the data. I have also created a sorted exted epression (=DateID) so that I can order the months in the correct order.

The problem is that is I have no mont seleceted everything is fine, but when I select the date the order changes on the graph. The data is all there it just changes the order and I cant work out why.

Any help would be greatly appreciated

Cheers

Simon

Re: Sorting using an expresion with Set Analysis

swuehl — Mon, 29 Oct 2012 15:12:07 GMT

Try

=only({1} DateID)

as sort expression (or even better, use a dual as dimension value (like create your Month using month() function and sort by numeric value).

Re: Sorting using an expresion with Set Analysis

jonathandienst — Mon, 29 Oct 2012 15:18:49 GMT

Simon

I assume that the set expression in your chart includes the month (Data) field. This is causing it to ignore the month selection (or possibly use the month selection to create a range of more than one month), but the sort table is now limited by the selection.

A better approach is to use a proper date field for the month. I your case, your fiscal year starts in April (I assume), so it might be easier to use a manually created dual. Something like this:

LOAD

Dual(Date, DateID) As Date

INLINE

[

Date, DateID

Apr, 1

May, 2

... (etc for Jun - Feb) ...

Mar, 12

];

There are more elegant ways, using a master calendar (search the forum for more information), but this is a quick & dirty way that should work. Now you just sort by Date using a numerical sort.

Hope that helps

Jonathan

Month	Data
May	100
Apr	150
Aug	200
Jul	20
Jun	50
Dec	80
Oct	120
Jan	130
Mar	125
Sep	180
Nov	160
Feb	90

Month	Data
May	100
Apr	150
Aug	200
Jul	20
Jun	50
Dec	80
Oct	120
Jan	130
Mar	125
Sep	180
Nov	160
Feb	90

Month	Data
May	100
Apr	150
Aug	200
Jul	20
Jun	50
Dec	80
Oct	120
Jan	130
Mar	125
Sep	180
Nov	160
Feb	90