Sorting Gantt chart by dates

[quiquehm]

Hi, I have a problem trying to sort the dimension values in a combo chart. I built kind of a Gantt chart to display project milestones across time, and I would need to sort the chart displaying the milestones as they will come in time. My dimension ( Task ) is a calculated dimension made of a string of simple dimensions and a few symbols ( Unicode symbols ) :

Task = =WBS_Nr_icon&' '&Activ_Nr_icon&'-'&CPS&' '&if(Not IsNull(date_MST_Actual),chr(9670),chr(9671))

Expressions are just 2 , Milestone Plan Date ( circles ) and Milestone Actual Date ( dots ). I can filter the chart to display only Plan dates ( open milestones ) ...so I would like to sort these by the dates , in ascending order.

I tried Sorting by date_MST_Plan, but it does not work. I also tried by Y-value, but no sucess.

Does anybody know how to do the trick ?

Thanks a lot

[JonnyPoole]

Are you able to share a qvw sample ? .. doesn't matter if scrambed.

[quiquehm]

Hi Jonathan,

Sure, here is the qvw

Appreciate any suggestions

Thanks

[JonnyPoole · Accepted solution]

i changed the sort to be by expression with this formula:  date(date_MST_Plan)

it seems to work with limitted testing.  To 2x check its right i added the date right at the start of the dimension on the left below.  The attached example doesn't show the date in the dimension but the sort was changed for your reference

[quiquehm]

Thank you Jonathan. In fact I tried that sort, but I forgot to add the date function to the date_MST_Plan, I thought the field itself would do the trick as I think the field has a date format already. Anyway I will use your sorting definitively. Thanks again

Rgds

[quiquehm]

By the way Jonathan, do you know how I could create a button or any kind of selector where I could change the type of sorting I want to apply to the chart ? like for example Sort by the dimension field ( alphabetically ) or Sort by the date ( as you showed me )

Thanks

Enrique

[JonnyPoole]

You can do that. I created a variable controlled by input box in attached. It has 2 values:  Date , ByDimension.

If you select Date, it will sort by the expression Date(date_MST_Plan) .  If you select Dimension the expression evaluates to null and then it will sort alphabetically (which will happen on the dimension by default)

If you wanted additional options, just create a nested IF statement to pick up different sort values.

[quiquehm]

Hi Jonathan, thanks for your help !. I am a bit confused now though, as your trick for sorting looked evident and straight forward to me , but when I opened your qvw file , the sorting does not work for me. Could it be due to different qvw versions ? I am still running the old 8.5 ( only one I have a license ). I put the plan dates in numeric format to see it more clear.

Any advice of what could be wrong ?

Thanks a lot

[JonnyPoole]

i have never used 8.5 but this is the area my last post was referring to. On the sort tabyou can enter an expression for sorting.  You would change what is below to  an IF() statement that chose a different sorting depending on the value of a variable.

[quiquehm]

Thanks ! I am not sure what I did before, but I tried again and it works. The only "problem" is if I remove the date from the dimension values ( the string ) , the sorting by date does not work. I will see if I can make those dates "transparent" or something similar, so they are not displayed on the chart.

Thanks again for your help