% of Business with variable in formula

[#1]

Hi,

I want to calculate the % of business each category represents within each customer.

I also determine the sales for This Year and Last Year using an input box and 2 variables vCurrentFinancialYear and vPrevFinancialYear.

To get the % of business I used this formula:

sum({$< FinYear = {$(#vCurrentFinancialYear)} >} (Sales))/ sum({$< FinYear = {$(#vCurrentFinancialYear)} >}total (Sales))

Customer	Customer One			Customer Two			Total
Category	Sales This Year	Sales Last Year	% Business	Sales This Year	Sales Last Year	% Business	Sales This Year	Sales Last Year	% Business
Category A	$1,744,519	$1,935,928	7.7%	$4,778,266	$4,999,583	21.1%	$6,522,785	$6,935,512	28.8%
Category B	$1,843,478	$2,052,198	8.1%	$5,169,134	$5,367,190	22.8%	$7,012,612	$7,419,388	31.0%
Category C	$1,306,998	$1,518,763	5.8%	$7,783,634	$9,669,775	34.4%	$9,090,633	$11,188,538	40.2%
Category D	$316	$7,364	0.0%	$13,234	$34,132	0.1%	$13,550	$41,496	0.1%
Total	$4,895,312	$5,514,252	21.6%	$17,744,268	$20,070,681	78.4%	$22,639,580	$25,584,933	100.0%

This works if you want to see the % of business a category represents in the total of all customers but I want to see the total within each customer as the example I mocked up below.

Customer	Customer One			Customer Two			Total
Category	Sales This Year	Sales Last Year	% Business	Sales This Year	Sales Last Year	% Business	Sales This Year	Sales Last Year	% Business
Category A	$1,744,519	$1,935,928	35.6%	$4,778,266	$4,999,583	26.9%	$6,522,785	$6,935,512	28.8%
Category B	$1,843,478	$2,052,198	37.7%	$5,169,134	$5,367,190	29.1%	$7,012,612	$7,419,388	31.0%
Category C	$1,306,998	$1,518,763	26.7%	$7,783,634	$9,669,775	43.9%	$9,090,633	$11,188,538	40.2%
Category D	$316	$7,364	0.0%	$13,234	$34,132	0.1%	$13,550	$41,496	0.1%
Total	$4,895,312	$5,514,252	100.0%	$17,744,268	$20,070,681	100.0%	$22,639,580	$25,584,933	100.0%

Does anyone know how to change my formula to do this?

Thanks

David

[#2 · Accepted solution]

Very short, use aggr()-function.

[#3]

Hi Thomas,

I haven't used aggr() before so at first I cursed your short post. But humbly I thank you as I had to learn the aggr() function myself which was a good learning experience.

I got it to work as follows if anyone else wants to do this:

sum({$< Year = {$(#vCurrentFinancialYear)} >} (Sales)) / sum(total <Customer> aggr(sum({$< Year = {$(#vCurrentFinancialYear)} >} (Sales)),Customer,Sales))

and I got this report, I now have a % each category makes up of total customers selected (in this case customer one and two) and also the % of business with in customer. (Just to save confusion I used a different period to my last post so thats why the values changed)

Customer	Customer One				Customer Two				Total
Category	Sales This Year	Sales Last Year	% of Total Business	% of Cust	Sales This Year	Sales Last Year	% of Total Business	% of Cust	Sales This Year	Sales Last Year	% of Total Business	% of Cust
Category A	$1,714,675	$1,991,799	7.7%	35.7%	$4,689,368	$5,149,433	21.0%	26.8%	$6,404,043	$7,141,231	28.7%	28.7%
Category B	$1,808,803	$2,111,731	8.1%	37.6%	$5,108,696	$5,502,117	22.9%	29.2%	$6,917,499	$7,613,848	31.0%	31.0%
Category C	$1,283,231	$1,550,986	5.8%	26.7%	$7,673,766	$9,856,193	34.4%	43.9%	$8,956,997	$11,407,179	40.2%	40.2%
Category D	$316	$7,364	0.0%	0.0%	$11,271	$34,881	0.1%	0.1%	$11,588	$42,244	0.1%	0.1%
Total	$4,807,026	$5,661,879	21.6%	100.0%	$17,483,101	$20,542,623	78.4%	100.0%	$22,290,127	$26,204,503	100.0%	100.0%

Thanks

David