Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 ;
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,
....
;
You could use: dual(monthname(YourTimestamp), num(month(YourTimestamp))) - further informations could you find here: How to use - Master-Calendar and Date-Values
- Marcus
First from scripting layer extract month from the above time stamp and then sort for 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
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.
I'm sure I quite get how to connect created fields with my date field ..
When I'm loading like this I have this error, maybe because it needs some other function because of postgres?
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 ;
Hi,
another solution could be:
Dual(Date(created_at,'MMMM'),Month(created_at)) as Month
hope this helps
regards
Marco