Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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.
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.
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?
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.
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...
HI Axon,
Can you attach a sample file. In general scenario it should work.
Deepak
Hey Hi,
Use this code in the Sort >> Expression Tab and select Ascending, remove all other checkboxes
=mid(QuarterYearField,2,1)
Hi,
Try this.
In sort tab you just select the load order and deselect every other selection.
Regards,
Kaushik Solanki
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
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