Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help getting going with Dates/Times/TimeBuckets

Hi,

Newbie question I'm afraid (yes, I'll search the community, but was hoping someone might take pity)

I have a file with a bunch of dates in this format;

December 1 2015 08:56 PM

I want to;

Add counts on hourly timebuckets

Add counts on days of the week,  Mon,Tues etc

Add counts on days of the month 1,2,3

Any help appreciated;

Thanks for looking.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

1) read your timestamps in, set the format code correctly or use Timestamp#() to interprete the timestamps

2) Create additional fields using date and time functions

3) create a chart with one of the new fields as dimension and count(FIELD) as expression

SET TimestampFormat = 'MMMM D YYYY hh:mm TT';

LOAD TimestampField,

           Hour(TimestampField) as Hour,

          Weekday(TimestampField) as WeekDay,

          Day(TimestampField) as Day

FROM ...;

Now create e.g. a chart with dimension Hour and as expression

=Count(Hour)

View solution in original post

6 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

it looks like your dates are stored as strings and not timestamps. An easy way to tell is to preview the data and see if the values appear right-justified (numbers and timestamps) or left-justified (strings).

In case your dates are strings, you need to convert the string to the proper timestamp data type. Something like this should work:

timestamp#(MyDate, 'MMMM D YYYY hh:mm TT')

Then, you might want to format the result using a new format pattern:

timestamp(timestamp#(MyDate, 'MMMM D YYYY hh:mm TT'), 'new format patten')

Once this is done, you can use QlikView Date functions to calculate Year, Month, WeekDay, Hour, and whatever else you need.

If you'd like to learn QlikView development techniques methodically, starting from the basics and going into the most advanced topics, check out my new book QlikView Your Business.

cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy and Austin, TX!

swuehl
MVP
MVP

1) read your timestamps in, set the format code correctly or use Timestamp#() to interprete the timestamps

2) Create additional fields using date and time functions

3) create a chart with one of the new fields as dimension and count(FIELD) as expression

SET TimestampFormat = 'MMMM D YYYY hh:mm TT';

LOAD TimestampField,

           Hour(TimestampField) as Hour,

          Weekday(TimestampField) as WeekDay,

          Day(TimestampField) as Day

FROM ...;

Now create e.g. a chart with dimension Hour and as expression

=Count(Hour)

maxgro
MVP
MVP

if your field is dt

Set LongMonthNames =        'January;February;March;April;May;June;July;August;September;October;November;December';


load

  dt, t,

  WeekDay(t) as weekday,

  Day(t) as day,

  floor(frac(t),1/24)*24 as hour;

load   dt,  timestamp(Timestamp#(dt, 'MMMM D YYYY hh:mm tt')) as t;

load * inline [

dt

December 1 2015 08:56 PM

December 1 2015 08:01 PM

December 1 2015 08:01 AM

December 1 2015 08:56 AM

December 1 2015 12:01 AM

];

1.png

Not applicable
Author

Thanks guys for taking the time to reply. I think you all contributed to me getting the right answer.

SET TimestampFormat = 'MMMM D YYYY hh:mm TT';

LOAD timestamp#(@1, 'MMMM D YYYY hh:mm TT'),

           Hour(timestamp#(@1, 'MMMM D YYYY hh:mm TT')) as Hour,

          Weekday(timestamp#(@1, 'MMMM D YYYY hh:mm TT')) as WeekDay,

          Day(timestamp#(@1, 'MMMM D YYYY hh:mm TT')) as Day,

          Month(timestamp#(@1, 'MMMM D YYYY hh:mm TT')) as Month,

          Week(timestamp#(@1, 'MMMM D YYYY hh:mm TT')) as Week,

          Year(timestamp#(@1, 'MMMM D YYYY hh:mm TT')) as Year

FROM [lib://qlikid_jpqlikqlik/demo.csv]

(txt, codepage is 1252, no labels, delimiter is ',', msq);

It works really well.

Next step is for me to work out how to have year+month, so Dec15 can be separated form Dec16.

I'll keep playing.

Thanks again.

swuehl
MVP
MVP

Have a look at the HELP, date and time functions:

Date and time functions ‒ Qlik Sense

Search for MonthName() or look into MonthStart() and format the returned date using Date() function.

Not applicable
Author

Thank You.