Multiple match statements for sorting

[#1]

Hi,

I'm trying to sort a chart that has a variable start date that can be selected by the user.

Sorting statement not working:

if(date(vStartDate,'M')= '1',match(Report_month, '1','2','3','4','5','6','7','8','9','10','11','12'),

if(date(vStartDate,'M')= '2',match(Report_month, '2','3','4','5','6','7','8','9','10','11','12','1'),

if(date(vStartDate,'M')= '3',match(Report_month, '3','4','5','6','7','8','9','10','11','12','1','2'),

if(date(vStartDate,'M')= '4',match(Report_month, '4','5','6','7','8','9','10','11','12','1','2','3'),

if(date(vStartDate,'M')= '5',match(Report_month, '5','6','7','8','9','10','11','12','1','2','3','4'),

if(date(vStartDate,'M')= '6',match(Report_month, '6','7','8','9','10','11','12','1','2','3','4','5'),

if(date(vStartDate,'M')= '7',match(Report_month, '7','8','9','10','11','12','1','2','3','4','5','6'),

if(date(vStartDate,'M')= '8',match(Report_month, '8','9','10','11','12','1','2','3','4','5','6','7'),

if(date(vStartDate,'M')= '9',match(Report_month, '9','10','11','12','1','2','3','4','5','6','7','8'),

if(date(vStartDate,'M')= '10',match(Report_month, '10','11','12','1','2','3','4','5','6','7','8','9'),

if(date(vStartDate,'M')= '11',match(Report_month, '11','12','1','2','3','4','5','6','7','8','9','10'),

if(date(vStartDate,'M')= '12',match(Report_month, '12','1','2','3','4','5','6','7','8','9','10','11',

match(Report_month, '9','10','11','12','1','2','3','4','5','6','7','8'))))))))))))))

Sorting statement working but not variable:

match(Report_month, '9','10','11','12','1','2','3','4','5','6','7','8')

Is there a way i can use the multiple if statements to select the prefered sorting?

Regards,

Coen

[swuehl]

So you want to show dimension values Report_month 1 to 12, but order chronological from a start date.

I think it would be better to create a dimension with a dual value, with a text representation 1 to 12 and an underlying numeric value that f.e. returns the start of the month. Thus sorting by numeric value should order your dimension values correctly.

Not 100% sure why your above doesn't work, maybe check the number of opening and closing brackets (I think you are missing one closing in the second last line).

Also check that

date(vStartDate,'M') returns what you expect, numeric letters from 1 to 12.

[#3]

This is not working because qlikview is still storing  date(vstartdate,'m') as the full date number (just formatting it as text) - try it for yourself:

=date(date#('21/11/2012'),'M')*1

returns 41234

Using the month() function would work better - but remember to compare it to an integer, not a string or it will try and compare the monthname/text with the number string (eg 'oct'='10')

=if(month(date#('21/11/2012'))=11,match(month(date#('21/10/2011'))*1, 11,12,1,2,3,4,5,6,7,8,9,10))

works.

But before you try that, have you considered using the mathematical operator mod()?

If you remember from high school maths, the moduls is the remainder when you divde one number by another. In this case the first integer by the second integer.

EG mod(9,5) returns 4 and mod(125,60) returns 5

It even works negatively eg mod(-18,5) returns 2 (because 5 divides -20 and then there are 2 "left over")

In your example you will need

=mod(Report_month-month(vStartDate),12)+1

IE get the difference in months, if it's negative then the modulus will "work around a clock" so to speak and get the right relative number for you. I've added 1 onto the end as mod will start from 0. (so if the months are the same it will return 0, and if it is the last month it will return 11)

Hope this works!

Erica