topic show total column once in pivot table in QlikView

show total column once in pivot table

karensmith — Fri, 16 Jul 2010 15:47:11 GMT

I am trying to reproduce this table in QV, but I am having some issues when creating Pivot table. I can not reproduce one column to represent total resale $$. The total column is diplayed six times for each one of the six customers. Is there a way to show total once. Does any one have any other suggesting to reproduce this information is usuable format that can easily be incorporated into end user presentations?

			Customer
		Total Resale ($M)	X	VV	YY	BB	CC	TT
Total past 12-mos:		$11.00	186.2%	199.9%	140.7%	159.9%	49.6%	292.8%
2009	June	$7.73	18.5%	21.0%	14.6%	12.2%	3.9%	29.8%
	July	$7.46	17.3%	18.2%	15.8%	16.2%	4.4%	28.2%
	Aug	$7.31	17.8%	16.1%	14.8%	16.0%	4.4%	30.8%
	Sep	$9.30	17.7%	19.4%	13.3%	13.0%	3.8%	32.7%
	Oct	$445.00	0.3%	0.3%	0.3%	0.4%	0.1%	0.5%
	Nov	$8.06	18.5%	19.0%	12.6%	15.2%	5.2%	29.6%
	Dec	$9.47	18.4%	22.0%	11.7%	12.9%	4.6%	30.4%
2010	Jan	$9.82	18.0%	22.3%	11.8%	16.6%	5.3%	26.0%
	Feb	$55.00	3.7%	4.0%	2.4%	3.1%	0.9%	5.7%
	Mar	$12.32	19.6%	20.4%	13.8%	14.0%	5.1%	27.2%
	Apr	$66.00	2.8%	2.9%	2.6%	3.2%	0.9%	4.6%
	May	$55.00	3.6%	3.9%	3.0%	3.6%	1.1%	5.2%
	June
	July
	Aug
	Sep
	Oct
	Nov
	Dec

Re. :show total column once in pivot table

martin59 — Fri, 16 Jul 2010 16:05:30 GMT

Hi,

You have to check this "show partial sum" in presentation tab on the right dimension to have your result.

show total column once in pivot table

karensmith — Fri, 16 Jul 2010 16:20:38 GMT

Thank you martin...I have used that feature, but I get a total for every customer... I just want to show the grand total once and that is it.

What do you think? Am I doing something wrong?

Market_Name

FYYear

FYMonthName

Sum (POSVALUE)

MarketShare

Sum (POSVALUE)

MarketShare

Sum (POSVALUE)

MarketShare

Sum (POSVALUE)

MarketShare

Sum (POSVALUE)

MarketShare

Sum (POSVALUE)

MarketShare

Total

$3,855,765.54

17.2%

$4,018,052.32

17.9%

$3,353,230.47

14.9%

$4,122,097.21

18.4%

$1,230,092.12

5.5%

$5,883,076.25

26.2%

2010

Total

$3,855,765.54

17.2%

$4,018,052.32

17.9%

$3,353,230.47

14.9%

$4,122,097.21

18.4%

$1,230,092.12

5.5%

$5,883,076.25

26.2%

2010

Apr

$1,851,312.08

8.2%

$1,885,775.06

8.4%

$1,708,099.62

7.6%

$2,137,385.84

9.5%

$598,372.42

2.7%

$3,032,457.78

13.5%

2010

May

$2,004,453.46

8.9%

$2,132,277.26

9.5%

$1,645,130.85

7.3%

$1,984,711.37

8.8%

$631,719.70

2.8%

$2,850,618.47

12.7%

Re. :Re: show total column once in pivot table

martin59 — Fri, 16 Jul 2010 16:23:30 GMT

if you try this feature on the market_name dimension ?

show total column once in pivot table

karensmith — Fri, 16 Jul 2010 16:44:09 GMT

	Market_Name	Total	Total	xx	xx	W	W	yy	YY
FYYear	FYMonthName	Sum (POSVALUE)	MarketShare	Sum (POSVALUE)	MarketShare	Sum (POSVALUE)	MarketShare	Sum (POSVALUE)	MarketShare
Total		$126,362,313.56	100.0%	NO TOTAL	18.0%	NO TOTAL	19.7%	NO TOTAL	13.8%
2009	Total	$103,899,999.65	82.2%	NO TOTAL	15.0%	NO TOTAL	16.5%	NO TOTAL	11.2%
2009	Apr	$6,533,086.29	5.2%	NO TOTAL	1.1%	NO TOTAL	1.2%	NO TOTAL	0.8%
2009	May	$6,624,286.46	5.2%	NO TOTAL	0.9%	NO TOTAL	1.1%	NO TOTAL	0.8%
2009	Jun	$7,725,196.49	6.1%	NO TOTAL	1.1%	NO TOTAL	1.3%	NO TOTAL	0.9%
2009	Jul	$7,458,072.34	5.9%	NO TOTAL	1.0%	NO TOTAL	1.1%	NO TOTAL	0.9%
2009	Aug	$7,308,736.39	5.8%	NO TOTAL	1.0%	NO TOTAL	0.9%	NO TOTAL	0.9%
2009	Sep	$9,301,650.95	7.4%	NO TOTAL	1.3%	NO TOTAL	1.4%	NO TOTAL	1.0%
2009	Oct	$8,420,752.66	6.7%	NO TOTAL	1.1%	NO TOTAL	1.1%	NO TOTAL	0.9%
2009	Nov	$8,060,705.69	6.4%	NO TOTAL	1.2%	NO TOTAL	1.2%	NO TOTAL	0.8%
2009	Dec	$9,467,968.22	7.5%	NO TOTAL	1.4%	NO TOTAL	1.7%	NO TOTAL	0.9%
2009	Jan	$9,824,093.24	7.8%	NO TOTAL	1.4%	NO TOTAL	1.7%	NO TOTAL	0.9%
2009	Feb	$10,851,751.14	8.6%	NO TOTAL	1.6%	NO TOTAL	1.7%	NO TOTAL	1.0%
2009	Mar	$12,323,699.78	9.8%	NO TOTAL	1.9%	NO TOTAL	2.0%	NO TOTAL	1.3%
2010	Total	$22,462,313.91	17.8%	NO TOTAL	3.1%	NO TOTAL	3.2%	NO TOTAL	2.7%
2010	Apr	$11,213,402.80	8.9%	NO TOTAL	1.5%	NO TOTAL	1.5%	NO TOTAL	1.4%
2010	May	$11,248,911.11	8.9%	NO TOTAL	1.6%	NO TOTAL	1.7%	NO TOTAL	1.3%
				Not show this column		Not show this column
				This data is confidential		This data is confidential

show total column once in pivot table

Fri, 16 Jul 2010 17:03:59 GMT

I got a tip:

Try to calculate a dimension that shows the Total Resale; you should do this with an AGGR expression.

Something like: "=AGGR(SUM(PosValue), Year,Month)" or "=AGGR(SUM(PosValue), MarketPlace)" depends what you are showing on that column.

This will let you emulate the first table that you shown us.

Hope it helps!

show total column once in pivot table

karensmith — Fri, 16 Jul 2010 18:54:28 GMT

I used Jermias suggestion to create an aggr dimension..,made a big difference. Retail sales is now showing grand total.

'retail sales'=num(AGGR(SUM(POSVALUE),FYYear,FYMonthName),'$#,##0;($#,##0)')

FYYear	Fiscal_YearMonthNum	retail sales	Market_Name	xx	yy	cc	ww	tt	ss
2009	200901	$6,533,086		20.6%	23.2%	15.0%	12.3%	4.1%	24.8%
2009	200902	$6,624,286		16.8%	21.9%	15.3%	16.5%	4.0%	25.6%
2009	200903	$7,725,196		18.5%	21.0%	14.7%	12.2%	3.9%	29.8%
2009	200904	$7,458,072		17.3%	18.2%	15.8%	16.2%	4.4%	28.1%
2009	200905	$7,308,736		17.8%	16.2%	14.8%	16.0%	4.4%	30.8%
2009	200906	$9,301,651		17.8%	19.3%	13.3%	13.0%	3.8%	32.7%

Now I am trying to figure out a way to toal each expression colum and then total retail sales....How would I total a dimension? Is that even possible?

Total past 12-mos:

$11.00

186.2%

199.9%

140.7%

159.9%

49.6%

292.8%

show total column once in pivot table

karensmith — Fri, 16 Jul 2010 19:15:13 GMT

FYYear	Fiscal_YearMonthNum	retail sales	Market_Name	XX	YY	WW	TT	CC	DD
Total				18.0%	19.7%	13.8%	15.6%	4.7%	28.1%
2009	200901	$6,533,086		20.6%	23.2%	15.0%	12.3%	4.1%	24.8%
2009	200902	$6,624,286		16.8%	21.9%	15.3%	16.5%	4.0%	25.6%
2009	200903	$7,725,196		18.5%	21.0%	14.7%	12.2%	3.9%	29.8%
2009	200904	$7,458,072		17.3%	18.2%	15.8%	16.2%	4.4%	28.1%

This is my latest view. One last total needed 'retailsales'.

show total column once in pivot table

karensmith — Thu, 22 Jul 2010 16:15:54 GMT

This is very frustrating...How Pivot tables in QV are so limited to calculating columns, i.e rolling total, *** totals.

I did create a dimension "retail sales" to calc total, but values can't create cumulative total becuase it is a calculated dimension. I just need one column that will display running total for Year/month sales.

Any other recommendations will be greatly appreciated!