Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a dimension which i need to sort the values are as follows
q111 which means q1 of year 2011,q211 which means q2 of 2011...
so it is scrambled as in q111,q314,q412,q212..and so on
I would like to sort it as
q111,q211,q311,q411,q112,q212,q312,q412,q113.. and so on
Can anyone help me build the expression for the sorting
Thanks
Hi marco, sorry for the late reply but I believe from reading the above proposed solutions the best way to do it would be by changing the script in the datamodel, I proposed some of these changes but my manager does not want to change the script he just wants me to come up with a direct sorting expression...so for now i am using a match expression
Have you tried one of the solutions as a calculated dimension? The following will provide what you need for the dimension, then set your expression as needed.
Dimension:
Dual(date,num(right(date,2) & right(left(date,2),1))) as date
Expression:
(your espression, such as Sum(Data)
--john
Hi I am new to qlikview so I might be doing this wrong but my original dimension is
=Right(Period,2)&Left(Right(Period,5),2)
which I changed to
Dual(Period,num(right(Period,2)&Left((right(Period,5),2))))
but this shows up as a syntax error
If Period is your date field name (for 'q111','q121', etc), then just substitute Period into my suggestion a la...
Dual(Period,num(right(Period,2) & right(left(Period,2),1))) as Period
I am sorry for not giving all the information before but "Period" originally has data as 2012 q1,2012 q2,2012 q3 but since the requirement was to show as q112,q212 the dimension that we used is =Right(Period,2)&Left(Right(Period,5),2)
and when I use your expression on top of that
Dual(Period,num(right(Period,2)&Left((right(Period,5),2)))) as Period
it gives me a syntax error on "as"
Hi,
given this information, you could also change my above mentioned solution into
=Dual(Right(Period,2)&Mid(Period,3,2), MakeDate(Left(Period,4), Right(Period,1)*3-2,1))
as an front end expression.
Here's an example of how using this expresion in either front or back end (supposed you could convince your manager 😉 could look like:
The big advantage of using the dual() function to seperate the presentation format from an internal numerical date value is, that sorting and using this field in date calculations does not require any additional efforts.
hope this helps
regards
Marco
Aliasing a field with 'as' is a backend script technique, so it's throwing an error in the front end.
TY this worked perfectly
you're welcome
regards
Marco