How to sort data by month?

[#1]

I need to create obj like this:

January	February	March	April	May	June	July	August	September	October	November	December

To sort data in pivot table by month. I have a date filed in database (created_at) which shows information in following format: 2015-07-08 12:45:02 (timestamp without time zone)

Thanks in advance!

@K N

Colin_Albert‌

[petter]

As long as you use the Month()-function in your load statement on a date-field you will get a dual-value which have both the textual part and the sort-order (month number) for each value. In short you will get what you ask for by using:

LOAD

          .....

          Month( SomeDateField ) AS Mnth,

         ....

;

[marcus_sommer]

You could use: dual(monthname(YourTimestamp), num(month(YourTimestamp))) - further informations could you find here: How to use - Master-Calendar and Date-Values

- Marcus

[satishkurra]

First from scripting layer extract month from the above time stamp and then sort for Month

[Colin-Albert]

Use the month function against a date field and the value will be stored as a dual value with the correct numeric value and text value. Sort the month field in numeric order to get the correct sort order.

In your load script use

     month(your_date_field) as Mth

You may need to use date# to get the correct date format initially

     month(date#(your_date_field, 'YYYY-MM-DD hh:mm:ss') as Mth

[petter]

A simple illustration of how to do it in an isolated field - this is easy to include in a full calendar:

Make sure that you resize the listbox with the resize handles so it gets much wider and only on row tall.

Finally turn off the Caption.

[#7]

I'm sure I quite get how to connect created fields with my date field ..

[#8]

When I'm loading like this I have this error, maybe because it needs some other function because of postgres?

[Colin-Albert · Accepted solution]

You are applying QlikView functions in your SQL script.

You need to use syntax like this

Sales:

load

       id,

       tenant_id,

     ...

     month(created_at) as Month.

     year(created_at as Year ;

SQL select

     id, tenant_id,

     ...

     from salescript_results ;

[MarcoWedel]

Hi,

another solution could be:

Dual(Date(created_at,'MMMM'),Month(created_at)) as Month

hope this helps

regards

Marco