topic Sorting by Date in App Development

Sorting by Date

Tue, 30 Aug 2016 10:43:18 GMT

Hi,

I need an help for a sorting problem.
The records is composed by lots of timestamp in the format 'DD/MM/YYYY hh:mm:ss[.fff]' called DataAggiornamentoOp.
I want to create a linechart with this dimension

day(timestamp(DataAggiornamentoOp)) &'-'& month(timestamp(DataAggiornamentoOp))&'-'& year(timestamp(DataAggiornamentoOp))

but I can't order the x-axis with the date, because of qlik understand this dimension like a "Text Field".

I can I solve this problem?

Re: Sorting by Date

sunny_talwar — Tue, 30 Aug 2016 10:50:28 GMT

May be change your dimension to this:

Date(Floor(DataAggiornamentoOp), 'DD-MM-YYYY')

Update: Added the Floor based on Toni's response (Thanks Toni for pointing out my mistake)

Re: Sorting by Date

Tue, 30 Aug 2016 10:56:58 GMT

I try every kind of transformation, but the problem is in the grouping of the linechart. When the timestamp data are aggregated (there is lots of data on a single date), the chart show the result correctly, but the x-axis become a string, and I can't select a correct sorting personalization

Re: Sorting by Date

ToniKautto — Tue, 30 Aug 2016 11:02:40 GMT

If the data is loaded properly as a timestamp, you need to convert the underlying numerical value to a integer. This is done by using the Floor() function on the timestamp.

Date(Floor(DataAggiornamentoOp), 'DD-MM-YYYY')

It is recommended that yo add this as a new field to the data model, so that you only have to prepare the data during reload.

Re: Sorting by Date

Tue, 30 Aug 2016 11:06:22 GMT

Perfcet, thanks Toni!