Is it possible to load a single date time column and then create multiple dimensions from it?

[#1]

Basically I have a database that displays the date/time as "YYYYMMDD HHMMSS". I understand how to load it and format it as MM/DD/YYY HH:MM:SS, but when creating visualizations it does not group the values how I want them to be. If I use the date/time dimension for a simple bar chart, the x-axis shows each unique entry. What I would like to do is have the x-axis display the year or month, and then drill down to the day from there. I can get this to work by editing the load scrip and creating new fields such as "Year" "Month" "day" and then creating a master dimension drilldown Year -> Month -> Day, but I assume Qlik Sense is smart enough to group these value together without having to split the date. Is this possible to do, and if so, what would be the best practice for doing this? Thank you again for you time.

[JonnyPoole · Accepted solution]

Hi William, you should build some date buckets in the load script.

Go to the load editor and do something like this:

change:

load

     [Datefield],

     ...

from <>

to:

load

     [Datefield],

    Year(floor([Datefield])) as Year,

    'Q' & ceil(  Month(floor([Datefield])) / 3) as Quarter,

    Month(floor([Datefield])) as Month,

     Date(floor([Datefield])) as Date,

     ...

from <>

The floor function is used to strip out the time aspect so that the bucketing loses the granularity you are getting but don't want.

Then you can create a master item / drill down dimensio nfrom Year to Quarter to Month to Date

Of course you can use these expressions in the UI, but it will calculate faster in the UI if you pre-calculate in the script first.  Hope it help!

[#3]

Thank you for your reply Jonathan. That was exactly what I needed!