12 Replies Latest reply: Jul 28, 2011 3:19 AM by Kaushik Solanki

# sorting a time dimension

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

• ###### sorting a time dimension

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.

• ###### Re: sorting a time dimension

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?

• ###### sorting a time dimension

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.

• ###### Re: sorting a time dimension

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

• ###### sorting a time dimension

HI Axon,

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

Deepak

• ###### Re: sorting a time dimension

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

• ###### Re: sorting a time dimension

HI,

How you have created a quarter-year field?

Did you used the concate function?

Or it is created at the time of creating the calander?

Regards,

Kaushik Solanki

• ###### Re: sorting a time dimension

hi kaushik,

i concatenated the QUARTER and YEAR fields of my calendar table in load script to form the new QUARTER-YEAR field

• ###### Re: sorting a time dimension

Hi,

Then istead of using any other formula you can select the load order in sort.

This way the order in which it is loaded in qlikview, that will be shown.

Regards,

Kaushik Solanki

• ###### sorting a time dimension

Hey Hi,

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

=mid(QuarterYearField,2,1)

• ###### Re: sorting a time dimension

Hi,

Try this.

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

Regards,

Kaushik Solanki

• ###### sorting a time dimension

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.

Another expample of using dual:

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

Hope this helps.

Regards,
Marcel