Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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.

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Help getting going with Dates/Times/TimeBuckets

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)

6 Replies

Re: Help getting going with Dates/Times/TimeBuckets

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!

MVP
MVP

Re: Help getting going with Dates/Times/TimeBuckets

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)

MVP
MVP

Re: Help getting going with Dates/Times/TimeBuckets

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

Re: Help getting going with Dates/Times/TimeBuckets

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.

MVP
MVP

Re: Help getting going with Dates/Times/TimeBuckets

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

Re: Help getting going with Dates/Times/TimeBuckets

Thank You.

Community Browser