Time Stamp

[evansabres]

Hello -

I have a field of dates for events that are formatted as 'MM/DD/YYYY'

This creates an issue when sorting chronologically as the first date becomes 01/DD/YYYY as soon as the calendar year changes.

My question is this. Is there a way to sort and trick Qlikview into not defaulting to sort as January (01) first.

Or, is there a way within the scripting to make a date so that the date is transformed to year first, 'YYYY/DD/MM'?

Thank you

[JonnyPoole]

use date#() to tell qlik how to interpret the source data values. then use date() to display the date values in a way that you want.

you can use them together too:

say your date values in the database are  01/20/2014 and you want to display 2014/20/01 do this

date(date#( [Date],'MM/DD/YYYY') , 'YYYY/DD/MM')

does this help ?

more formatting help attached,

[Anonymous]

Not clear what the problem is.  If the field is sorted chronologically, and January 1st is the earliest date, it should be the first in the sort order.

???

[evansabres]

January is first in terms of the calendar, however, my events run from October - June, so for my purposes, October is first chronologically

[Anonymous]

Add "sort by state, ascending".

If it doesn't help - need more info.  Maybe upload your app.

[JonnyPoole · Accepted solution]

it may help to use a fiscal calendar technique for this

http://community.qlik.com/message/514789#514789

[Colin-Albert]

Do all your events run from October as the year start?

If so then adding a calendar like the Fiscal Calendar example here may help.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/05/28/fiscal-year

[Anonymous]

The OP never mentioned that the year starts in October, so I thought it was about selections.  Yes, it very well could be a fiscal year question.