topic Date Grouping in QlikView

Date Grouping

vksthkkr — Fri, 20 Nov 2020 18:02:30 GMT

Hi,

I am new to Qlikview and am working on a personal project to understand QV better.

I have had the standard QV training available n-line but need a little help.

I am importing transaction data from an SQLite Table and there is a field called TRANSACTION_DATE that I have imported.

I have also created a seperate calendar table using the script below:

[Calendar]:
LOAD *, Month(TRANSACTION_DATE) as TRANSACTION_MONTH, Year(TRANSACTION_DATE) as TRANSACTION_YEAR;
LOAD date(fieldvalue('TRANSACTION_DATE',RecNo())) as TRANSACTION_DATE AUTOGENERATE fieldvaluecount('TRANSACTION_DATE');

My concern is that this will group dates as months but the sequencing of TRANSACTION_MONTH will be lost once I record transactions in January'2012. In short, JAN, FEB etc... will start apperating before SEP, OCT, NOV, DEC on the chart.

I would like the dates to be represented as MMM-YY on the dimesion axis so that the sequence of TRANSACTION_MONTH can be maintained.

I am attaching here, an excel chart representation of how I would like the data to be seen in QV:

Date Grouping

rohit214 — Thu, 29 Dec 2011 07:42:13 GMT

hi there

just make a composite key

load

Month(TRANSACTION_DATE)&'-'& Year(TRANSACTION_DATE) as key

from,,,,

may it helps you

thanks

rohit

Date Grouping

Sokkorn — Thu, 29 Dec 2011 08:41:28 GMT

Hi,

I'm not sure you try this yet : DATE(TRANSACTION_DATE,'MM-YY') AS [%KEY]

Regards,

Sokkorn Cheav

Date Grouping

vksthkkr — Thu, 29 Dec 2011 09:27:15 GMT

Thanks Rohit.

I used your logic and created a composite date key as below:

Year(TRANSACTION_DATE) & '-' & num(Month(TRANSACTION_DATE)) as TRANSACTION_YEARMONTH

Date Grouping

Thu, 29 Dec 2011 09:47:12 GMT

Hi,

Add a calculated Dimension in a bar chart.... & write...

=Date(TRANSACTION_DATE,'MMM-YY')

Regards,

Parag