sorting a time dimension

[#1]

hi guys, this is a follow-up from a previous discussion here http://community.qlik.com/message/136417#136417

i currently am not sure how i can sort the fields in my cyclic group

my cyclic group has these fields: year, quarter-year, quarter, month, week

the charts look fine if u look at a single year, so the months will sort themselves numerically from 1 to 12 and quarter-year will appear from Q1-2011 to Q2-2011 and so on...

however, if i choose 2010 and 2011, i see quarter-year Q1-2010 Q1-2011 Q2-2010 Q2-2011 Q3-2010 Q3-2011 Q4-2010 in this order...

how can i make it appear in this order? Q1-2010 Q2-2010 Q3-2010 Q4-2010 Q1-2011 Q2-2011 Q3-2011

the order does not change if i include the date field in the Sort tab under expression...

[deepakk]

hi,

One way to achieve it would be.

1) Create YearMonth field like 201001, 201002, 201003...... etc

2) Use it in the sort tab expresion =YearMonth.

I hope this helps you out.

[#3]

hi Deepak thanks for your reply,

i understand your method but i realised my month/weeks are 1 2 3 4 5 instead of 01 02 03 04 05 for those that are below 10

so eventually the concatenated number may be incorrect in values already

how could i add a 0 without affecting the appearance of 1 2 3 4 5 (i don't want to see 01 02 03 04 05 on the charts) or is there another workaround?

[deepakk · Accepted solution]

hi,

You can use num function to get zero .

eg :  Num(Month,'00') wil give you 01,02.

The YearMonth which we will create will be used at sort tab function for sorting . It will not be displayed in front of the chart

On front of Chart it will be Q1-2010 , Q2-2010 and Sort tab-->Expression it will be =YearMonth.

I hope you got my point.

[#5]

hmmm i thought it would work but it still didnt...

now the order becomes Q1-2011 Q3-2011 Q2-2011 Q4-2010 Q3-2010 Q1-2010 Q42-2010 when i select year 2010 and 2011 from list boxes...

i've also added a week field but it shouldnt affect the order right...

[deepakk]

HI Axon,

Can you attach a sample file. In general scenario  it should work.

Deepak

[rahulgupta]

Hey Hi,

Use this code in the Sort >> Expression Tab and select Ascending, remove all other checkboxes

=mid(QuarterYearField,2,1)

[kaushiknsolanki]

Hi,

   Try this.

   In sort  tab you  just select the load order and deselect every other selection.

Regards,

Kaushik Solanki

[#9]

Hi,

I always use the dual function in het load statement. With it you can combine text and a number or date.

dual('Q1 2010',201001) as fieldname

When you sort the numeric value will be used and in the display the text will be used.

The original article where I first read about dual:

http://www.qlikfix.com/2010/12/27/creating-a-custom-sort-order-load-order-dual/

Another expample of using dual:

http://blog.qvapps.com/2010/09/10/qlikview-tutorial-vertical-labels-inside-bars/

Hope this helps.

Regards,
Marcel

[#10]

hi all, thanks for the help

the order works fine when i use YEARQUARTER instead of YEARMONTH in the new field

i'm not really sure why, can any of you enlighten me?

i shall describe the situation again:

- i have a calendar table other than the data tables

- the calendar table is necessary since we operate on a different week system as QV

- the calendar table has WEEK, MONTH, QUARTER, YEAR integers and other fields include the DATE

- i made a new field QUARTER-YEAR in order to achieve my objective as stated in the link in my 1st post

- i have a cyclic group for all charts, the cyclic group has YEAR, QUARTER, QUARTER-YEAR, MONTH and WEEK as its dimension