topic Re: Re: Totals in pivot table in QlikView

Totals in pivot table

Anonymous — Fri, 21 Nov 2014 15:00:47 GMT

Hi,

I have searched through previous discussions but I haven't been able to find the answer to my particular question. I want to the totals to appear for each year rather than an overall total. I have tried ticking the partial sums I don't get the totals for the year (name, year boundary are all dimensions).

This is what I get

	Boundary	Total	High	High	High	Inlier	Inlier	Inlier	OneDay	OneDay	OneDay
Name	Year		2010	2011	2012	2010	2011	2012	2010	2011	2012
A		125	-	3	3	42	34	41	1	-	1
B		43	1	-	1	9	12	15	2	-	3
C		16	-	-	-	4	5	4	1	1	1

This is what I want

	Boundary	Total	Total	Total	High	High	High	Inlier	Inlier	Inlier	OneDay	OneDay	OneDay
Name	Year	2010	2011	2012	2010	2011	2012	2010	2011	2012	2010	2011	2012
A		43	37	45	-	3	3	42	34	41	1	-	1
B		12	12	19	1	-	1	9	12	15	2	-	3
C		5	6	5	-	-	-	4	5	4	1	1	1

Re: Totals in pivot table

Anonymous — Fri, 21 Nov 2014 15:21:06 GMT

Try useing this expression Sum({$<year={'2010'}>}sales)

Regards

Harsha

Re: Totals in pivot table

Anonymous — Fri, 21 Nov 2014 15:29:16 GMT

Thanks for your reply. I have only shown a small portion of the data, there are a lot of years in the data and I didn't want to have to add an expression for each year. Do you think that adding an expression is the only way?

Kind regards,
Emer

Re: Totals in pivot table

Anonymous — Fri, 21 Nov 2014 16:42:17 GMT

can you post some sample data

Re: Totals in pivot table

ecolomer — Sun, 23 Nov 2014 07:51:15 GMT

Is that you need?

Re: Totals in pivot table

stevedark — Sun, 23 Nov 2014 21:52:28 GMT

If you drag the dimensions in the Pivot you can change what is totalled where - so you could group by year and get a total for each year.

Another approach may be to look at creating an association in the data model High, Inlier and OneDay to themselves, and then also to a Total grouping, an inline like this will do this:

SumTotal:

Load

Boundary,

BoundaryTotal

INLINE [

Boundary,BoudnaryTotal

High,High

Inlier,Inlier

OneDay,OneDay

High,Total

Inlier,Total

OneDay,Total

];

You then just use the BoudaryTotal as a dimension rather than Boundary.

I've explained this more fully in this blog post:

http://www.quickintelligence.co.uk/qlikview-accumulate-values/

Steve

Re: Totals in pivot table

Anonymous — Mon, 24 Nov 2014 09:33:34 GMT

Yes this is what I need

Re: Totals in pivot table

Anonymous — Mon, 24 Nov 2014 09:41:37 GMT

Thanks Steve, no matter which way I dragged the columns I couldn't get the totals right. I have used the boundary total approach and it works great.

Re: Re: Totals in pivot table

ecolomer — Mon, 24 Nov 2014 13:08:26 GMT

The file QV with the example

Re: Re: Totals in pivot table

Anonymous — Mon, 24 Nov 2014 15:00:49 GMT

Thanks Enrique this is very helful

Re: Totals in pivot table

Mon, 24 Nov 2014 15:51:19 GMT

Partial sums on your year dimension might give you what you're looking for.