Can the sorting in Grid within Visual Report be done by date instead of by string?

I really like the filtering possible in Visual Report Grids but have a couple of questions:

1 Once I have filtered my data and sorted by a date field, the dates are not in the correct ordered. They seem to be sorted as text. Can I sort dates into the correct order? Sometimes sorting by the submission date is a workaround but this does not work for, say, peoples Birth Dates.

Thanks

Charlie

I see Charlie that this is related to your form titled Sheep Actions.

Now since we can not answer several questions in one thread, you should always post a single issue / question per thread.

I have however now moved the other questions to their new threads here:

The question about the weight vs date has been moved here: http://www.jotform.com/answers/563268

The question about the removal of form title from Grid was moved here: http://www.jotform.com/answers/563269

In regards to the date sorting, I agree that it should be sorted by date and not as a string and I see this as a bug, so I will raise this as a bug to our developers for inspection and for a fix.

Once there are any updates you will be notified about it through this thread.

Thank you Ben,

I will look forward to seeing an update at some stage.

Please can you remove the reference to my form so that I don't get unwanted responses.

Regards

Charlie

As requested I removed the reference to your form.

If you need further assistance please let us know as we're here to help.

Thank you Carina

A workaround to sort dates in Visual Report Grids:

In an effort to find a workaround for the problem of sorting dates (dates sort as strings) in the Visual Report Grids I made an new date field (Sort Date) and set it to be populated from my original date field (Action Date) with a Update via a Condition. I set Sort Date to the format yyyymmdd which, I reasoned, should sort truly even as a string sort. The update seemed to have worked e.g. if Action Date was 28/04/2015, Sort Date became 2015-04-28. That should sort better, I thought.

However when sorting by Sort Date it still does not sort correctly but I am puzzled as to why it sorts as it does.

This is the sort by Action Date - clearly a string sort:

This is the sort by Sort Date: how does it come up with this??:

Thinking that this would surely work I manually updated about 50 records. Silly thing to do!!

This is the link to my report: http://www.jotform.com/report/51218476928060

However I then thought that perhaps the separators were the problem, so introduced another hidden field (Date Sort Text). I then used Conditions to take out the separators to give just the digits of the date.  e.g. I converted 2015-04-28 from Date Sort to 20150428 in Date Sort Text. This time I just tested on a few records.

And finally, success.  I can now sort the dates in Visual Report Grid truly!!   Very happy:

Perhaps someone out there knows of a better way to achieve a workaround for this. I would be happy to hear of any other solutions.

The best solution of course would be for the developers to get on to this so that the dates sort truely in the first place.  : )

Now I need to go and manually update the 50 records again!!

Regards

Charlie

Thank you Charlie for adding the screenshots and doing tests. I am a bit puzzled as to why it would sort it like it did since the dash "-" is the same for all dates and as such should not have made a difference.

The only idea that comes to mind is to check if a space " " would make any difference when sorting the dates - instead of a dash, but the way you did the tests would be the only way to do this at this time.

Our developer is however already assigned to this so as soon date sorting is introduced, you will be notified about it through this thread.

Hi Ben,

I suspect that the string sort is interrupted by non-alphanumerics such as / or - as in dates. Thus dates in the format dd/mm/yyyy sort by the first two characters which in my example gave a partial sort. When the date was in the format yyyy-mm-dd and the year was the same in all dates the sort routine defaulted to the order that they were submitted - i.e. the submission date. I have not checked this, having just thought of it.

Thank you for your help.

Regards

Charlie

Unfortunately I can not check this since we do not have access to the backend code, but I would have guessed that first all non numbers are removed and then the rest is used to sort the values (like you have set in your last screenshot).

Hopefully our developers will resolve this so that it is very simple for you to select the requires options and sorting without any workarounds.

As soon as they do, we will be happy to update you about it here.

Omur,

Thank you for removing the headings from the Visual Report. That looks much better.  : )

I hope that you are still working on the sorting of dates in the Visual Report Grids as my 'solution' is messy and just a workaround for an issue that is a bug.

It is a fundamental function in any data retrieval system to be able to sort by date. Please don't give up on us here!!

Regards

Charlie

Hi there Omur,

Thank you for getting back to me.

I have filtered the Test Report to show dates covering two months in two grids:

The top grid is sorted by Action date and is incorrect (April should be before May),

The bottom grid is sorted by Sort Date but sorts only the characters before the dash (-), which are all the same, then defaults to sorting by the Submission Date.

Sort Date Text would sort correctly, so I have not demonstrated that, as the string and numeric sorts are the same.

What we need is to be able to sort by Action date correctly - i.e. as a Date not as a String.

Cheers

Charlie