Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.