Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Not applicable

How to sort data by month?

I need to create obj like this:

JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember

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

1 Solution

Accepted Solutions
Highlighted

Re: How to sort data by month?

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 ;

9 Replies
MVP
MVP

Re: How to sort data by month?

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,

         ....

;

MVP & Luminary
MVP & Luminary

Re: How to sort data by month?

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
Valued Contributor II

Re: How to sort data by month?

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

Re: How to sort data by month?

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

MVP
MVP

Re: How to sort data by month?

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

2015-09-15 #3.png

2015-09-15 #1.png

2015-09-15 #2.png

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.

Not applicable

Re: How to sort data by month?

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

Not applicable

Re: How to sort data by month?

screenshot 10.png

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

Highlighted

Re: How to sort data by month?

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?

Hi,

another solution could be:

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

QlikCommunity_Thread_180814_Pic1.JPG

QlikCommunity_Thread_180814_Pic2.JPG

hope this helps

regards

Marco