topic AW:Re: Group by Month - Charts in QlikView

Group by Month - Charts

Tue, 19 Apr 2011 17:03:10 GMT

Dears,

I have another problem.

How can I group by month the values in my chart. Coz actuallu it is showing every date like (01/01/2010, 01/02/2010, 01/03/2010...) I need to group it by month.

Follow attached is the example.

Could you please help me?

Group by Month - Charts

matt_crowther — Tue, 19 Apr 2011 17:06:05 GMT

I'll take a wild guess: add either a Calculated Dimension under the Dimensions tab of =monthname(MyDateField) or add the same expression into your load script:

Table1:

Load FieldA,

monthname(MyDateField) as Month

From XYZ.xls

Hope that solves it,

Matt - Visual Analytics Ltd

Group by Month - Charts

Tue, 19 Apr 2011 20:57:51 GMT

Sorry,

I didn't understand it, could you do an example for me please.

It's my first time using QlikView and I'm completely confused.

Thanks for instance per your assistence.

Have a nice day

AW:Re: Group by Month - Charts

Tue, 19 Apr 2011 22:29:45 GMT

Hello kellyhbastos

within the script-languange of QV exist a lot of functions to format, transform, calculate data. In your case you should take a look at the date-functions. There is a Month()-function you can use for your purpose to create a new field containing the Month of the Date during the load.


LOAD
Date,
Month(Date) AS Month,
Year(Date) AS Year,
Value
FROM ....

The code above will create two new fields called Month and Year to use as a dimension in a chart, (as you want it to do) or in an expression (if you need to). For more details see help and.or your manuals.

Regards, Roland

AW:Re: Group by Month - Charts

Wed, 20 Apr 2011 12:58:13 GMT

Thanks Again Roland,

It's working perfectly!

Bye

AW:Re: Group by Month - Charts

IAMDV — Wed, 20 Apr 2011 13:25:11 GMT

Please can you mark the post verified, if this works for you 🙂

TIA!

AW:Re: Group by Month - Charts

Wed, 20 Apr 2011 14:15:20 GMT

It worked, but there is another problem.

Since I'm using the expression "=sum([Mont])" and I have values from different years, the software is suming the values of January 2010 and January 2011. How can I separate it?

Maybe I should have to concatenate (Month) and (year)

How can I do it/

Thanks

AW:Re: Group by Month - Charts

Miguel_Angel_Baeyens — Wed, 20 Apr 2011 14:19:30 GMT

There are different ways of doing that.

But basically, the Date() function will allow you to say how do you want to display any given date, for example

Date(DateField, 'MMMYY')

wil return "jan10", "jan11", "feb10" and so on.

Hope that helps.

AW:Re: Group by Month - Charts

Wed, 20 Apr 2011 14:20:39 GMT

try load

AW:Re: Group by Month - Charts

Wed, 20 Apr 2011 14:27:39 GMT

I didn't worked here.
how can I concatenate Month and Year?

Thanks

AW:Re: Group by Month - Charts

matt_crowther — Wed, 20 Apr 2011 14:36:50 GMT

Instead of Rolands 'month(Date)' use Monthname(Date) as I first suggested, it will return MMM-YY.

Hope that helps,

Matt - Visual Analytics Ltd

AW:Re: Group by Month - Charts

Wed, 20 Apr 2011 15:28:38 GMT

Try Month(Date,...)&Year(Date,...) as MONTHYEAR

AW:Re: Group by Month - Charts

Wed, 20 Apr 2011 19:27:34 GMT

Matt Crowther,

Your solution really worked to me, but in order to reduce the chart size, I would like to write (01-11) and not (January - 11).

How can I do it?

Thanks

AW:Re: Group by Month - Charts

Thu, 21 Apr 2011 11:47:59 GMT

use this