Percentage based upon group totals

[2ffat]

Let's see if I can explain this so you can help me.

I am trying to create a report that will create a percentage based upon another group's totals. For example, my company creates widgets that are sold to other companies around the world. Some widgets designed for overseas (a market) are being sold in the US while some designed for the US (another market) are sold overseas. My boss want to know what percentage of widgets sold in each state or country based upon the total of widgets sold in a market. I get the data based upon sales date then group my subtotals by market, country, and state where market is the first group and state is the third group.

My formula might look like: (Total_State_Widgets_For_Market1 / Total_Widgets_for_Market1) * 100. My problem is how do I get the Total_Widgets_for_Market1. I've tried using WhileReadingRecords but it gives me bogus numbers. I'm either using it incorrectly or am missing the something.

James P. Cottingham

When a man sits with a pretty girl for an hour, it seems like a minute. But let him sit on a hot stove for a minute and it's longer than any hour. That's relativity.
[tab][tab]Albert Einstein explaining his Theory of Relativity to a group of journalists.

 

[cmpgeek]

are you creating a distinct count subtotal per grouping and a grand total of the number of widgets? if so that should give you the numbers you need... if i am thinking it through correctly

good luck - i hope this helps...

ShannonLea

 

[lbass]

One of these should work, depending on whether your widget totals are usual type summaries or running totals:

Distinctcount({widget},{state})%Distinctcount({widget},{market})//Place in Group (State) Footer or Header

If you are using running totals, I think you need to create running totals for states that are reset on change of market, which means you have to establish specific conditions for each running total, such as:

Using the running total editor, select {widget}, count or distinctcount, evaluate on change of formula:

{state} = "Massachusetts"

Reset on change of Market. Then you can use the formula:

#MassWidgets%#Market

This must be placed in the Group (Market) Footer to work correctly. The problem, of course, is the number of specific running totals you must create, along with text boxes identifying them in the Group (Market) Footer.

-LB

 

[2ffat]

Thanks, I'll give it a try and let you know the results.



James P. Cottingham

When a man sits with a pretty girl for an hour, it seems like a minute. But let him sit on a hot stove for a minute and it's longer than any hour. That's relativity.
[tab][tab]Albert Einstein explaining his Theory of Relativity to a group of journalists.

 

[2ffat]

They just gave me another curve. Instead of wanting a count of widgets sold, they want the percentage based upon invoice dollars. DistictCount won't work. Any other ideas?



James P. Cottingham

When a man sits with a pretty girl for an hour, it seems like a minute. But let him sit on a hot stove for a minute and it's longer than any hour. That's relativity.
[tab][tab]Albert Einstein explaining his Theory of Relativity to a group of journalists.

 

[lbass]

Sum({widget.invamt},{state})%Sum({widget.invamt},{market})//Place in Group (State) Footer or Header

or

Using the running total editor, select {widget.invamt}, sum, evaluate on change of formula:

{state} = "Massachusetts"

Reset on change of Market. Then you can use the formula:

{#MassWidgetSalesTotal}%{#MarketWidgetSalesTotal}

You would have to adjust your running total if you have duplicate records. You could try using instead a formula like:

(If not onfirstrecord then
{Invoice#} <> previous({Invoice#}) and
{state} = &quot;Massachusetts&quot;

-LB

 

[2ffat]

I'm sorry, I didn't make myself clear enough. My problem is because these totals are being evaluated when the records are printing, I don't have the market total or grand total yet. I need to get the market total and grand total while records are being read. Then when I get to the state group, I can get my percentage.

James P. Cottingham

When a man sits with a pretty girl for an hour, it seems like a minute. But let him sit on a hot stove for a minute and it's longer than any hour. That's relativity.
[tab][tab]Albert Einstein explaining his Theory of Relativity to a group of journalists.

 

[Ngolem]

Post the formula you are using .... we should be able to do the sum in that formula allocating it to a variable for later display

Jim Broadbent

 

[lbass]

James--

Well, that's why you need to place the results for the state groups in the group footer for market--then the market total will already have been tallied. Did you try this? It should work.

-LB