Sort by expression

Fri, 12 Apr 2013 07:35:32 GMT

Hello ,

I've got an issue regarding custom sort in a bar chart . I've got a calculated dimension where i group a bunch of dates based on a specific interval and some expressions like Sum(Sales) and Sum(Cost).

=if(date(MonthYear,'MM-YYYY')>=AddMonths(progMax,-2) and date(MonthYear,'MM-YYYY')<=progMax

,date(AddMonths(progMax,0),'MMM-YY')&' - '&date(AddMonths(progMax,-2),'MMM-YY'),

if(date(MonthYear,'MM-YYYY')>=AddMonths(progMax,-5) and date(MonthYear,'MM-YYYY')<=AddMonths(progMax,-3)

,date(AddMonths(progMax,-3),'MMM-YY')&' - '&date(AddMonths(progMax,-5),'MMM-YY'),

if(date(MonthYear,'MM-YYYY')>=AddMonths(progMax,-8) and date(MonthYear,'MM-YYYY')<=AddMonths(progMax,-6)

,                                   date(AddMonths(progMax,-6),'MMM-YY')&' - '&date(AddMonths(progMax,-8),'MMM-YY')
                                                         )
                                           )
                                  )

progMax is a variable that reprezents the maximum date of MonthYear . If MonthYear is in the specified interval i will show the begining and the specified interval.

The dimension works fine but I can't sort it ascending or descending. For example i'd like to sort it from "date(AddMonths(progMax,-6),'MMM-YY')&' - '&date(AddMonths(progMax,-8),'MMM-YY')" up to "date(AddMonths(progMax,0),'MMM-YY')&' - '&date(AddMonths(progMax,-2),'MMM-YY')".

I've also tried Sort By Expression ascending or descending using the following code.

=if(date(MonthYear,'MM-YYYY')>=AddMonths(progMax,-2) and date(MonthYear,'MM-YYYY')<=progMax

if(date(MonthYear,'MM-YYYY')>=AddMonths(progMax,-5) and date(MonthYear,'MM-YYYY')<=AddMonths(progMax,-3)

if(date(MonthYear,'MM-YYYY')>=AddMonths(progMax,-8) and date(MonthYear,'MM-YYYY')<=AddMonths(progMax,-6)

,3)
)
)

The sort by expression doesn't seem to work using this code.

Any suggestions ?

Thanks.

topic Sort by expression in QlikView

Sort by expression