Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. 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

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 ;

View solution in original post

9 Replies
Highlighted
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?

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 ;

View solution in original post

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