topic sorting a time dimension in QlikView

sorting a time dimension

Thu, 28 Jul 2011 02:26:24 GMT

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

deepakk — Thu, 28 Jul 2011 02:46:40 GMT

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

Thu, 28 Jul 2011 03:26:44 GMT

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?

sorting a time dimension

deepakk — Thu, 28 Jul 2011 03:50:34 GMT

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

Thu, 28 Jul 2011 06:16:46 GMT

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

deepakk — Thu, 28 Jul 2011 06:48:48 GMT

HI Axon,

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

Deepak

sorting a time dimension

rahulgupta — Thu, 28 Jul 2011 06:51:22 GMT

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

kaushiknsolanki — Thu, 28 Jul 2011 06:52:20 GMT

Hi,

Try this.

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

Regards,

Kaushik Solanki

sorting a time dimension

Thu, 28 Jul 2011 06:58:24 GMT

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

Re: sorting a time dimension

Thu, 28 Jul 2011 07:05:40 GMT

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

kaushiknsolanki — Thu, 28 Jul 2011 07:08:22 GMT

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

Thu, 28 Jul 2011 07:16:06 GMT

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

kaushiknsolanki — Thu, 28 Jul 2011 07:19:14 GMT

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