Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

How do I group survey dates (by day)?

I've imported a CSV with a lot of survey data--each line is a survey observation.

There's a timestamp column in the MM-DD-YYYY HH-MM-SS-MS format.  I'm trying to group the timestamps by day, so that my survey data can show satisfaction aggregated by day.  How do I do this?

Also, I think related to this, is there a source online (or in Qlik Sense) where I can find all the possible functions that can be used to create an expression?

Tags (2)
5 Replies
awhitfield
Esteemed Contributor

Re: How do I group survey dates (by day)?

Hi Aurangzeb,

ou have a timestamp, e.g. ‘2012-01-28 08:32:45’ in your transaction table and you want to link this to a master calendar table containing dates. One correct way to load this key could be

Date( Floor( Timestamp#( Created, 'YYYY-MM-DD hh:mm:ss')), 'M/D/YYYY') as CreatedDate

Regards

Andy

Not applicable

Re: How do I group survey dates (by day)?

Thanks Andrew.  I'll give that a shot.

Where can I find all the different possible functions I can use?  I spent quite some time trying to find it online last night, but no luck.

Not applicable

Re: How do I group survey dates (by day)?

Andrew, if the variable (column) this date sits in is called 'timestamp', can I effect that variable directly, as an example:

Date( Floor( Timestamp#( Created, 'timestamp')), 'M/D/YYYY') as CreatedDate

awhitfield
Esteemed Contributor

Re: How do I group survey dates (by day)?

Change where is says Created to the name of your field

Date( Floor( Timestamp#( Created, 'YYYY-MM-DD hh:mm:ss')), 'M/D/YYYY') as CreatedDate

Andy

Employee
Employee

Re: How do I group survey dates (by day)?

One thing to add , in Qlik Sense it can be helpful to create the date calendar (using all of the above date functions) using DERIVED fields.  These are new to version 1.1 Qlik Sense

https://help.qlik.com/sense/en-US/online/#../Subsystems/Hub/Content/Scripting/DerivedFields.htm?High...

Then the date fields will show up in the UI in a specialized location  ('Date & Time fields') as below...

A little easier to organize and about the same amount of work in the load editor.

Untitled.png