topic Excluding one dimension from total in QlikView

Excluding one dimension from total

Sat, 19 May 2012 19:05:14 GMT

I have a table splitting my data according to two dimensions, one being the year of a payment, the second being a calculated dimension that splits the payment into £500 bandings.

I then show a count of the number of individuals in each payment banding for each year.

I would like to add a final column showing the total number of individuals each year, without segmenting by payment banding.

So far I have managed to use a set expression (see table for syntax) to show the total number of individuals overall, but not segmented by year. I also think that my set expression will cause filters not to work. How can I amend my set expression to exclude one dimension, but not the other?

Hopefully this all makes sense. I'm fairly new to Qlikview.

£500Income Bands	Tax Year Ended	=sum(Active)	sum( {1} total Active)
0 <= x < 500	2012	10	825
500 <= x < 1000	2012	5	825
1000 <= x < 1500	2012	2	825
1500 <= x < 2000	2012	1	825
2000 <= x < 2500	2012	1	825
3000 <= x < 3500	2012	1	825
0 <= x < 500	2013	12	825
500 <= x < 1000	2013	3	825
1000 <= x < 1500	2013	4	825
1500 <= x < 2000	2013	2	825
2000 <= x < 2500	2013	1	825
0 <= x < 500	2014	10	825
500 <= x < 1000	2014	4	825
1500 <= x < 2000	2014	5	825
2000 <= x < 2500	2014	1	825
2500 <= x < 3000	2014	1	825
3000 <= x < 3500	2014	1	825

Excluding one dimension from total

swuehl — Sat, 19 May 2012 22:05:55 GMT

You can add a field list to your total qualifier to achieve what you want:

=sum(total<[Tax Year Ended]> Active)

=sum({1} total<[Tax Year Ended] Active)

to disregard any selections.

Re: Excluding one dimension from total

Sun, 20 May 2012 09:35:16 GMT

Thanks

Trying the above gave me the following (applied to slightly different data):

£500 Bands	Tax Year Ended	Sum (Active)	=sum({1} total<[Tax Year Ended]> Active)
3000 <= x < 3500	2011	1	1
2000 <= x < 2500	2011	1	1
1000 <= x < 1500	2011	1	1
500 <= x < 1000	2011	1	1
0 <= x < 500	2012	6	6
-500 <= x < 0	2012	5	5
-1000 <= x < -500	2012	3	3
-1500 <= x < -1000	2012	1	1

To be clear about what I'm aiming for, I want the total by year in the last column (i.e. 4 for all of the 2011 rows and 15 for all of the 2012 rows).

I think the problem is that I'm trying to exclude the £500 banding, which is a dimension, not a selection. Any help would be hugely appreciated.

Thanks

John

Re: Excluding one dimension from total

swuehl — Sun, 20 May 2012 09:46:18 GMT

I think I have understood your requirements.

I assumed [Tax Year Ended] is your actually field name, not the label you may have given to the dimension, right?

As said, you can add a field list to the total qualifier, it won't work with a label etc.

Re: Excluding one dimension from total

Sun, 20 May 2012 11:20:32 GMT

Tax Year Ended is an actual field name.

"£500 bands" is a label for a calculated dimension based on the following class expression:

=CLASS(AGGR(Sum (if([Movement Type]='Repayments', [Amount])), [Tax Year Ended], [Individual ID]),[Repayment Bandings])

Does that help?

Excluding one dimension from total

swuehl — Sun, 20 May 2012 11:52:47 GMT

Interesting. I now can reproduce your issue.

Please try

=aggr(NODISTINCT sum(TOTAL<[Tax Year Ended]> Active), [Tax Year Ended])

as expression.