topic Re: Time Stamp in QlikView

Time Stamp

evansabres — Fri, 06 Mar 2015 16:18:36 GMT

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

Re: Time Stamp

JonnyPoole — Fri, 06 Mar 2015 16:29:17 GMT

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,

Re: Time Stamp

Anonymous — Fri, 06 Mar 2015 16:36:19 GMT

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.

???

Re: Time Stamp

evansabres — Fri, 06 Mar 2015 16:43:53 GMT

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

Re: Time Stamp

Anonymous — Fri, 06 Mar 2015 16:46:12 GMT

Add "sort by state, ascending".

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

Re: Time Stamp

JonnyPoole — Fri, 06 Mar 2015 16:57:28 GMT

it may help to use a fiscal calendar technique for this

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

Re: Time Stamp

Colin-Albert — Fri, 06 Mar 2015 16:58:22 GMT

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

Re: Time Stamp

Anonymous — Fri, 06 Mar 2015 17:00:59 GMT

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.