Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

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

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:MMSmiley FrustratedS, 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.

1 Solution

Accepted Solutions
Employee
Employee

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

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!

2 Replies
Employee
Employee

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

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!

Not applicable

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

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