crosstab conversion of sums into %

[jurgen]

Hi,

I've created a crosstab that gives me per location per client per year the volumes that they bought, and the total for all the locations so

year client total loc(1) loc(2) loc(3) ... loc(m)
2004 x 10000 1000 5000 4000 0
2004 y 15000 5000 0 6000 4000
....

this is the result based on a query that gives me
2004 x loc(1) 1000
2004 x loc(2) 5000
2004 x loc(3) 4000
2004 y loc(1) 5000
2004 y loc(1) 5000
2004 y loc(3) 6000
2004 y loc(m) 4000
....

Eery month there are new clients and new locations so it has to dynamical

My problem is that they want it in % so the result must finally be:

year client total loc(1) loc(2) loc(3) ... loc(m)
2004 x 10000 10% 50% 40% 0%
2004 y 15000 33,33% 0% 40% 26,67%
....

How can i created this, i've tried several solutions but without any results. Can anyone give me advice,

Thanx a lot

JJ

 

[jurgen]

One more thing the total must be 100% instead of the total volume

 

[woodlandstx]

Jurgen -

I just finished a similar problem, but all of my data was unrelated.

I created one Cross Tab for Awarded and one for Lost, then added the Lost Cross-Tab to the Awarded and linked the EnteredDate.

Then saved and made a third Cross-Tab from that one.

Here is the formula I used to do my percentage in a text box on my form:

=+[11- Awarded Proposals Value Count_Crosstab1.Total Of Est Value]/Abs([11- Awarded Proposals Value Count_Crosstab1.Total Of Est Value]+[11- Lost Dead Cancelled Proposals Count_Crosstab1.Total Of Est Value])

I hope this helps....it may be a bit confusing as my tables and the cross-tabs get to be long.

Maybe this will help a bit?