Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sorting using an expresion with Set Analysis

Hi,

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

MonthData

May

100
Apr150
Aug200
Jul20
Jun50

Dec

80
Oct120
Jan130
Mar125
Sep180
Nov160
Feb90

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

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

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

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

2 Replies
swuehl
MVP
MVP

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).

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein