topic Group data in QlikView

Group data

Wed, 29 Sep 2010 16:29:14 GMT

Hello!! After two hors without success, some idea how show this?
	DATA	DATE	NAME	AMOUNT
		15/09/2010	GEORGE	40
		11/10/2010	JANE	10
		19/10/2010	GEORGE	115
		28/10/2010	GEORGE	900
		01/11/2010	JANE	256
		12/11/2010	JANE	200
		14/12/2010	GEORGE	100
		23/12/2010	JANE	25

	I need show this.

		Sep-10	Oct-10	Nov-10	Dec-2010	Total
	JANE	0	10	456	25	491
	GEORGE	40	1015	0	100	1155


	Thanks Regards César Estrada

Group data

pover — Wed, 29 Sep 2010 16:46:23 GMT

1. Create a pivot table with two dimensions:

Name, monthname(DATE)

and one expression

sum(Amount)

2. Expand the first column of the pivot table.

3. Drag the second column of the pivot table to above the expression column (sometimes this is a little tricky to do) and when a horizontal blue arrow appears let go of the mouse.

4. You have your report.

Regards.

Group data

Miguel_Angel_Baeyens — Wed, 29 Sep 2010 16:47:25 GMT

Hi César,

Do a pivot table with NAME and

=Date(DATE, 'MMM-YY')

as Dimensions and

Sum(AMOUNT)

You will have to pivot the month columns.

Hope this helps.

Group data

Wed, 29 Sep 2010 16:50:11 GMT

Hi,

Do this as a pivot table with Name and Date as dimensions and then sum(Amount) as the expression.

To get the months to group like that, edit the Date dimension like this:

=MONTH(DATE)&'-'&YEAR(DATE)

Hope that helps!

Cheers, Emma

Group data

Wed, 29 Sep 2010 16:52:23 GMT

Just read Karl's post ... monthname(DATE) is better than my way! 🙂

Group data

Wed, 29 Sep 2010 16:55:23 GMT

not sure using the date format will work - when I've tried it before this just gives me several columns for each month rather than totalling them up. Only works for me if I include monthstart as well ... then the formula becomes

=DATE(MONTHSTART(DATE),'MMM-YYYY')

Group data

Wed, 29 Sep 2010 16:58:03 GMT

Thanks!!!! work perfect!!!!

Regards

César

Group data

pover — Wed, 29 Sep 2010 17:08:45 GMT

Agreeing with Emma, the date function usually maintains the original number value of the date, even though the format changes. I've had this problem in the script.

Regards.