topic Re: How to sort data by month? in QlikView

How to sort data by month?

Tue, 15 Sep 2015 13:29:20 GMT

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‌

Re: How to sort data by month?

petter — Tue, 15 Sep 2015 13:35:59 GMT

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,

....

;

Re: How to sort data by month?

marcus_sommer — Tue, 15 Sep 2015 13:38:08 GMT

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

- Marcus

Re: How to sort data by month?

satishkurra — Tue, 15 Sep 2015 13:39:08 GMT

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

Re: How to sort data by month?

Colin-Albert — Tue, 15 Sep 2015 13:54:22 GMT

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

Re: How to sort data by month?

petter — Tue, 15 Sep 2015 13:57:59 GMT

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.

Re: How to sort data by month?

Tue, 15 Sep 2015 14:28:03 GMT

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

Re: How to sort data by month?

Tue, 15 Sep 2015 14:29:50 GMT

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

Re: How to sort data by month?

Colin-Albert — Tue, 15 Sep 2015 14:37:14 GMT

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 ;

Re: How to sort data by month?

MarcoWedel — Tue, 15 Sep 2015 22:55:31 GMT

Hi,

another solution could be:

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

hope this helps

regards

Marco