Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sort by expression

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

,1


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


,2


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.

0 Replies