100% Stacked bar with aggregate

[ift_isabelle]

Hi,

I have customer data about their spend TV, Radio and Internet on a specific date, like in this table

Customer	Mediumtype	Date	Spend
A	TV	1/1/2016	€ 100
A	TV	2/1/2016	€ 600
A	Radio	1/1/2016	€ 250
A	Internet	3/1/2016	€ 20
B	TV	1/1/2016	€ 80
B	Radio	1/1/2016	€ 65

I want to make a 100% stacked bar chart per customer split on the mediumtypes. Like this:

I tried to make an aggregation, but none of these work, it's not adding up to 100%:

sum({<year=2016,"Media Type"=>}Spend)/ aggr(sum({<year=2016,"Media Type"=>}Spend),Customer)

aggr(sum({<year=2016>}Spend), [Media Type], Customer)/ aggr(sum({<year=2016>}Spend), Customer)

sum({<year=2016>}Spend)/ sum(aggr(sum({<year=2016>}Spend),  Customer))

sum({<year=2016>}Spend)/ sum(aggr(sum( {<year=2016>}Spend), Customer))

sum({<year=2016>}Spend)/ sum( { total <year=2016>}Spend))

What am I doing wrong? Can you help me?

Thanks!

Kind regards,

Isabelle

[Gysbert_Wassenaar]

Try sum(Spend)/sum(TOTAL <Mediumtype> Spend)

[ift_isabelle]

I tried sum(Spend)/sum(TOTAL <Customer> Spend) and that worked perfect!

Thanks!

[jagan · Accepted solution]

Hi,

Try

=sum({<year={'2016'}>}Spend)/ sum( { <year={'2016'}>} TOTAL <Customer> Spend)

Hope this helps you.

Regards,

Jagan.