topic Re: Sorting Gantt chart by dates in QlikView

Sorting Gantt chart by dates

quiquehm — Fri, 05 Sep 2014 16:28:06 GMT

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

Re: Sorting Gantt chart by dates

JonnyPoole — Fri, 05 Sep 2014 19:58:22 GMT

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

Re: Re: Sorting Gantt chart by dates

quiquehm — Mon, 15 Sep 2014 12:12:54 GMT

Hi Jonathan,

Sure, here is the qvw

Appreciate any suggestions

Thanks

Re: Re: Re: Sorting Gantt chart by dates

JonnyPoole — Mon, 15 Sep 2014 12:47:30 GMT

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

Re: Sorting Gantt chart by dates

quiquehm — Mon, 15 Sep 2014 20:32:11 GMT

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

Re: Sorting Gantt chart by dates

quiquehm — Wed, 17 Sep 2014 20:22:00 GMT

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

Re: Re: Sorting Gantt chart by dates

JonnyPoole — Thu, 18 Sep 2014 00:30:43 GMT

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.

Re: Re: Re: Sorting Gantt chart by dates

quiquehm — Fri, 19 Sep 2014 11:00:21 GMT

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

Re: Sorting Gantt chart by dates

JonnyPoole — Fri, 19 Sep 2014 13:24:01 GMT

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.

Re: Sorting Gantt chart by dates

quiquehm — Fri, 19 Sep 2014 16:22:36 GMT

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