Sum up subtotals before visualising in bar chart

[jannarous05]

I am visualising basic family income data.

So i have a household id number, name of income earner, gender of income earner, and amount of income per earner, like this:

HHID, name, gender, amount
1, John, m, 300
1, Sally, f, 400
1, Maria, f, 450
2, Steve, m, 2000
2, Rory, m, 500
3, Martha, f, 750
3, Arla, f, 1000

So then I want to show a bar chart of the income per household (not income per earner).

So the bar chart should show:

HH 1 = 1150

HH2 = 2500

HH3 = 1750

Thanks if you can help me with this!

[agigliotti]

you can use the below settings for your bar chart:

- HHID as dimension field

sum(amount) as expression measure.

[jannarous05]

This isn't quite what I was hoping for.

I have about 500 households - so it isn't a huge dataset, however, this gives me a bar chart of over 500 bars, which doesn't allow me to actually categorise the results.

So I'd rather have the measure as the count of the number of the households.

And the dimension as the household income amount.

Does that make sense?

[thi_pham]

You can use aggregate calculated dimension:

Dimension: aggr(sum(amount), hhid)
Measure: count(hhid)

For better visualization, you should bin the dimension to just around under 10 ranges. 

like

if(aggr(sum(amount), hhid) < 1000, [0,1000),

if(aggr(sum(amount), hhid) < 2000, [1000,2000),

if(aggr(sum(amount), hhid) < 3000, [2000,3000),

...