Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
You can add a field list to your total qualifier to achieve what you want:
=sum(total<[Tax Year Ended]> Active)
or
=sum({1} total<[Tax Year Ended] Active)
to disregard any selections.
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
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.
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?
Interesting. I now can reproduce your issue.
Please try
=aggr(NODISTINCT sum(TOTAL<[Tax Year Ended]> Active), [Tax Year Ended])
as expression.