Easy Crystal Question on Percents

Yahoo Archive
1
I'm not sure what you want for the final results, but I know you could do this with an Executive Query if you have that license. It is about the only thing Executive Query does well; summarize data.




________________________________
From: Jonathan Lang <lsbestinc@...>
To: vantage@yahoogroups.com
Sent: Thursday, September 3, 2009 10:07:31 AM
Subject: [Vantage] Easy Crystal Question on Percents

Â
Â
Â
I need a formula that will take the percentage of two sumarized fields. for example:
Â

Customer
2008 Sales
2009 Sales

%Difference

ABC
$3,000.00
$10,000

233.30%
Â
I tried the usual "2009slaes / 2008sales * 100 - 100" but the fields are sumarized, so I think that's why it doesn't work.
Â
Thank you for your help!
Â
Jonathan LangÂ
Oil Rite Corporation
Sr. Network Administrator
Ph: (920) 682-6173 Ext: 126
Fax: (920) 682-7699

    Â

[Non-text portions of this message have been removed]







[Non-text portions of this message have been removed]
2
Â
Â
I need a formula that will take the percentage of two sumarized fields. for example:
Â










Customer
2008 Sales
2009 Sales

%Difference

ABC
$3,000.00
$10,000

233.30%
Â
I tried the usual "2009slaes / 2008sales * 100 - 100" but the fields are sumarized, so I think that's why it doesn't work.
Â
Thank you for your help!
Â
Jonathan LangÂ
Oil Rite Corporation
Sr. Network Administrator
Ph: (920) 682-6173 Ext: 126
Fax: (920) 682-7699

    Â




[Non-text portions of this message have been removed]
3
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Jonathan Lang
Sent: Thursday, September 03, 2009 9:08 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Easy Crystal Question on Percents



(Sum(2009sale)/Sum(2008sale))*100-100



I think this is what you want? Where 2009sale = 1000,2000,3000,4000
which totals to your 10,000 for this customer for example?



I need a formula that will take the percentage of two sumarized fields.
for example:


Customer
2008 Sales
2009 Sales

%Difference

ABC
$3,000.00
$10,000

233.30%

I tried the usual "2009slaes / 2008sales * 100 - 100" but the fields are
sumarized, so I think that's why it doesn't work.

Thank you for your help!

Jonathan Lang
Oil Rite Corporation
Sr. Network Administrator
Ph: (920) 682-6173 Ext: 126
Fax: (920) 682-7699



[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
4
Also, don't forget to put in a test for zero in the denominator to avoid divide by zero errors. One more thing - the Sum function needs to know what group you are summing over. Thus: (Sum(2009Sales,Customer)/Sum(2008Sales,Customer)*100)-100.

So, in Basic syntax, the formula might look something like this (air code):

Formula = 0
If Sum(2008Sales,Customer) <> 0 then
Formula = (Sum(2009Sales,Customer)/Sum(2008Sales,Customer)*100)-100
End if

Thom Rose
Controller
Electric Mirror LLC
HOTEL LUXURY

"The World Leader in Back-lit Mirrors & Mirror TV Technology"

T 425 776-4946
A 11831 Beverly Park Rd, Bldg D, Everett, WA 98204 USA
www.electricmirror.com<http://www.electricmirror.com>


From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Karen Schoenung
Sent: Thursday, September 03, 2009 7:17 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Easy Crystal Question on Percents




From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf
Of Jonathan Lang
Sent: Thursday, September 03, 2009 9:08 AM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Easy Crystal Question on Percents

(Sum(2009sale)/Sum(2008sale))*100-100

I think this is what you want? Where 2009sale = 1000,2000,3000,4000
which totals to your 10,000 for this customer for example?

I need a formula that will take the percentage of two sumarized fields.
for example:


Customer
2008 Sales
2009 Sales

%Difference

ABC
$3,000.00
$10,000

233.30%

I tried the usual "2009slaes / 2008sales * 100 - 100" but the fields are
sumarized, so I think that's why it doesn't work.

Thank you for your help!

Jonathan Lang
Oil Rite Corporation
Sr. Network Administrator
Ph: (920) 682-6173 Ext: 126
Fax: (920) 682-7699

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]



[Non-text portions of this message have been removed]
5
Yeah, I tried that and got repeating. All of the percents are 45.2 all the way down.
Â


--- On Thu, 9/3/09, Karen Schoenung <kschoenung@...> wrote:


From: Karen Schoenung <kschoenung@...>
Subject: RE: [Vantage] Easy Crystal Question on Percents
To: vantage@yahoogroups.com
Date: Thursday, September 3, 2009, 9:16 AM


Â





From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On Behalf
Of Jonathan Lang
Sent: Thursday, September 03, 2009 9:08 AM
To: vantage@yahoogroups .com
Subject: [Vantage] Easy Crystal Question on Percents

(Sum(2009sale) /Sum(2008sale) )*100-100

I think this is what you want? Where 2009sale = 1000,2000,3000, 4000
which totals to your 10,000 for this customer for example?

I need a formula that will take the percentage of two sumarized fields.
for example:


Customer
2008 Sales
2009 Sales

%Difference

ABC
$3,000.00
$10,000

233.30%

I tried the usual "2009slaes / 2008sales * 100 - 100" but the fields are
sumarized, so I think that's why it doesn't work.

Thank you for your help!

Jonathan Lang
Oil Rite Corporation
Sr. Network Administrator
Ph: (920) 682-6173 Ext: 126
Fax: (920) 682-7699

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]



















[Non-text portions of this message have been removed]
6
Thank you Thomas!

--- On Thu, 9/3/09, Thomas Rose <t.rose@...> wrote:


From: Thomas Rose <t.rose@...>
Subject: RE: [Vantage] Easy Crystal Question on Percents
To: "vantage@yahoogroups.com" <vantage@yahoogroups.com>
Date: Thursday, September 3, 2009, 9:27 AM


Â



Also, don't forget to put in a test for zero in the denominator to avoid divide by zero errors. One more thing - the Sum function needs to know what group you are summing over. Thus: (Sum(2009Sales, Customer) /Sum(2008Sales, Customer) *100)-100.

So, in Basic syntax, the formula might look something like this (air code):

Formula = 0
If Sum(2008Sales, Customer) <> 0 then
Formula = (Sum(2009Sales, Customer) /Sum(2008Sales, Customer) *100)-100
End if

Thom Rose
Controller
Electric Mirror LLC
HOTEL LUXURY

"The World Leader in Back-lit Mirrors & Mirror TV Technology"

T 425 776-4946
A 11831 Beverly Park Rd, Bldg D, Everett, WA 98204 USA
www.electricmirror. com<http://www.electric mirror.com>

From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On Behalf Of Karen Schoenung
Sent: Thursday, September 03, 2009 7:17 AM
To: vantage@yahoogroups .com
Subject: RE: [Vantage] Easy Crystal Question on Percents

From: vantage@yahoogroups .com<mailto:vantage% 40yahoogroups. com> [mailto:vantage@yahoogroups .com<mailto:vantage% 40yahoogroups. com>] On Behalf
Of Jonathan Lang
Sent: Thursday, September 03, 2009 9:08 AM
To: vantage@yahoogroups .com<mailto:vantage% 40yahoogroups. com>
Subject: [Vantage] Easy Crystal Question on Percents

(Sum(2009sale) /Sum(2008sale) )*100-100

I think this is what you want? Where 2009sale = 1000,2000,3000, 4000
which totals to your 10,000 for this customer for example?

I need a formula that will take the percentage of two sumarized fields.
for example:

Customer
2008 Sales
2009 Sales

%Difference

ABC
$3,000.00
$10,000

233.30%

I tried the usual "2009slaes / 2008sales * 100 - 100" but the fields are
sumarized, so I think that's why it doesn't work.

Thank you for your help!

Jonathan Lang
Oil Rite Corporation
Sr. Network Administrator
Ph: (920) 682-6173 Ext: 126
Fax: (920) 682-7699

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]



















[Non-text portions of this message have been removed]