topic Re: Create a bar chart sorted by month in QlikView

Create a bar chart sorted by month

Thu, 31 Dec 2015 08:44:25 GMT

Hi, I 'm new in qlikview. I have different dates in one of the column and i would like to create a bar chart sorted by month. I appreciate if anyone can help me

thanks in advance

Re: Create a bar chart sorted by month

oknotsen — Sun, 03 Jan 2016 14:13:33 GMT

Dates are usually stored as number (though showing as date). I suggest sorting your chart on that column by number.

Re: Create a bar chart sorted by month

MarcoWedel — Sun, 03 Jan 2016 15:04:46 GMT

please post sample data/application to demonstrate with.

regards

Marco

Re: Create a bar chart sorted by month

Peter_Cammaert — Sun, 03 Jan 2016 15:10:47 GMT

You will have to calculate a month value from your date column in your load script, like

LOAD ...

DateColumn,

Month(DateColumn) AS MonthColumn,

Amount

FROM whereveryouwant (options);

Now you can create a bar chart with dimension MonthColumn and expression =sum(Amount).

Alternatively, you could create a calculated dimension =Month(MonthColumn) and use it with the same sum expression. But that won't be the most efficient solution (performance-wise). The script solution is the-right-thing-to-do.

Best,

Peter

Re: Create a bar chart sorted by month

ecolomer — Sun, 03 Jan 2016 17:03:53 GMT

Hi,

I attached you an exmaple (but is in spanish language. I'm sorry).

Re: Create a bar chart sorted by month

mightyqlikers — Mon, 04 Jan 2016 05:20:43 GMT

create an inline table with month and month_num field with 1 to 12 .

then sort by month_num

Regards

$@M.

Re: Create a bar chart sorted by month

HirisH_V7 — Mon, 04 Jan 2016 05:39:01 GMT

Hi ,

Data:

LOAD * ,

Month(Date) As Month,

Num(Month(Date)) as MonthNum

INLINE [

Date, Sales

01/01/2015, 1

01/02/2015, 2

01/03/2015, 4

01/04/2015, 1

01/05/2015, 6

01/06/2015, 3

01/07/2015, 6

01/08/2015, 9

01/09/2015, 11

01/10/2015, 6

01/11/2015, 7

01/12/2015, 9

];

MonthNum_Sort:

LOAD * INLINE [

MonthNum, MonthNum2

1, 1

2, 2

3, 3

4, 4

5, 5

6, 6

7, 7

8, 8

9, 9

10, 10

11, 11

12, 12

];

Here in the above data the Monthnum_Sort is taken but it also can be done at front end in the bar chart ,Dimension Sorting.

Output:

PFA,

HTH,

-Hirish

Re: Create a bar chart sorted by month

Mon, 04 Jan 2016 08:26:33 GMT

Many thanks. I'll try it out and let you know..

Re: Create a bar chart sorted by month

Mon, 04 Jan 2016 08:32:29 GMT

Thank you for your reply, I have "Hire Date" as field showing dd/mm/yy. The records that I am currently working on is approximately about 12,000.

I want to have the year as YYYY and the month as MMM to show in the right order. The year should not repeat based on records.

I've tried the load as per your comments but it gives me an error.

Would you please show me a step by step process.

Regards

Re: Create a bar chart sorted by month

Mon, 04 Jan 2016 08:49:53 GMT

Thanks Enrique

Re: Create a bar chart sorted by month

HirisH_V7 — Mon, 04 Jan 2016 11:06:01 GMT

Fine

Re: Create a bar chart sorted by month

Peter_Cammaert — Mon, 04 Jan 2016 11:17:52 GMT

What error message do you get when executing the load script?

Re: Create a bar chart sorted by month

Mon, 04 Jan 2016 11:30:35 GMT

I did not logged down the error message. I will try to do it again and post the result