not conditional 1

[sandora]

CRV11, Access DB
I have an income statement with groups for Revenues, COS, etc. I have the account numbers summaried and the groups summaried and a grand total. I now need to divide the COS summary total by the revenue total for a percentage. I can get Revenues to work with the following formula, but the rest of the groups are seen as zero.

if {@AcctNames} = "Revenues" then Sum ({@Amount}, {@AcctNames}) else 0

How do I make this a value and not a conditional statement?

 

[synapsevampire]

Please post what you want and where. Saying that you have summaries doesn't describe where they are.

I think that you just want a summary in the group footer with:

(Sum ({@Amount}, {@AcctNames}) / Sum ({@Amount}))*100

-k

 

[sandora]

Sorry, I was trying to be brief, too brief I guess.

This is what I have:

Account Description Acct# Amount

Revenues
New NLQ Trailer 4020-15 -128,730 25.08%
New LQ Trailer Sales 4020-25 -146,432 28.53%
Used Trailer Sales - 4021-15 -30,750 5.99%
Sales, Freightliners 4023-15 -200,240 39.01%

Total Revenues (513,308) 1.00
COS
COS, New NLQ 5220-15 109,354 23.92%
COS, New LQ Trailer 5220-25 101,446 22.19%

Total COS 457,076 0.00
SGA
Salaries & Wages - FL 7000-15 5,455 9.60%
Health Ins - FL 7001-15 773 1.36%
Commissions - FL 7002-15 8,539 15.03%

Admin Fees - FL 7305-15 2,820 4.96%
Maintenance - FL 7306-15 5,965 10.50%
Total SGA 56,816 0.00
Other
Misc Income - FL 8030-15 2,600 31.82%

Document Fees - FL 8302-15 -495 -6.06%
Trade Payoff Exp - FL 8307-15 52 0.64%
Total Other 8,173 0.00
($8,756)

Total Revenue, COS, SGA, Other are in GF1 Acctnames, the others are in GF2 Acctnumbers. In the % column for total revenue, etc, they need to be divided by total Revenue. The 1.00 next to the Revenue and 0 next to the others is the result of the afore mentioned formula. Does this help?

 

[lbass]

Insert a running total {#Revenue} using the expert. Choose sum of {table.amount}, evaluate using a formula:

{@AcctNames} = "Revenues"

Reset never. Then create a formula:

sum({table.amount},{@AcctNames}) % {#Revenue}

Place this in the group footer (AcctNames).

-LB

 

[sandora]

I inserted the running total {#Revenue} but it won't let me choose any sum, it says "Invalid Field Selection". I'm not trying to second guess anyone but couldn't you somehow declare 'Revenues' to be separate from the other groups. I don't know, I'm grasping at straws at this point.

 

[sandora]

Nevermind I'm braindead this morning. I used @Amount and it worked just fine. Thanks so much.

 

[sandora]

In the example below the problem is with the percentages. All are correct except the ones for Revenues. The total is correct, but the New NLQ, LQ, etc are using a running total and I need them to use the Total Revenues. The other groups are using the Total Revenues total which is correct. The formula I'm using is

(Sum ({@Amount}, {GL_Account.GL Account Number})/{#Revenue})*100 & "%"

Account Description Acct# Amount
Revenues
New NLQ Trailer 4020-11 -131,355 100.00%
New LQ Trailer Sales 4020-21 -181,622 58.03%
Used Trailer Sales - 4021-11 -83,245 21.01%
Sales, Freightliners 4023-11 -199,469 33.49%
Sales Utility & 4024-11 -76,081 11.33%
PDI Income - OKC 4025-11 -2,100 0.31%
Sales, Service - OKC 4026-11 -9,670 1.41%
Sales Over 4029-11 8,500 -1.26%
Warranty Reimburse - 4030-11 -3,002 0.44%
Sales, Sponsorships 4040-11 17,796 -2.70%
Sales, Parts - OKC 4100-11 -12,003 1.79%
Total Revenues (672,251) 100.00%
COS
COS, New NLQ 5220-11 120,230 -17.88%
COS, New LQ Trailers 5220-21 154,180 -22.93%
Total COS 604,649 -89.94%
SGA
Salaries & Wages - 7000-11 13,618 -2.03%
Health Ins - OKC 7001-11 3,295 -0.49%
Total SGA 88,821 -13.21%
Other
Misc Income - OKC 8030-11 -51 0.01%
Interest Exp - OKC 8205-11 2,897 -0.43%
Total Other (1,313) 0.20%
($19,906)

 

[lbass]

Create a formula {@Revrev}:

if {@acctnames} = "Revenue" then {table.amt}

Then your formula to get the percentages would be:

{table.amt} % sum({@Revrev})

You actually could use this for all the percentages, instead of my earlier suggestion.

By the way, you should be able to use the % sign as I am doing instead of the "/", multiplying by 100, and then adding the sign. All you then need to do is format it to add the percent by clicking on the % icon.

-LB

 

[sandora]

I can't seem to get this to work, I'm coming up with some very odd percentages. I don't know if makes a difference or not but all of these are summaries of the account number detail. I tried to change the formula a bit:

if {@acctnames} = "Revenue" then sum {@amount, accountnames}

but when I put the @revrev formula in the summary, it says it can't be summarized. Is there a better way to do this?

 

[lbass]

In your previous post, each line represents a detail line, except for those starting with "Total", correct? If so, my suggestion should work unless you are using either section suppression or group selection. You should not change the formulas.

-LB

 

[sandora]

No, each line is a summary of a suppressed detail line for each account number, the total is a second summary for accountnames. I'm very sorry I didn't make that clear at the beginning.

 

[lbass]

If you don't have detail lines you are excluding for whatever reason from the summaries at the GL_Account_Number level, then you should be able to use my earlier formula {@Revrev}:

if {@acctnames} = "Revenue" then {@amount}

The percentage formula for the group (GL_Account_Number) should be:

sum({@amount},{GL_Account.GL Account Number}) % sum({@Revrev})

The percentage formula for the group (acctnames) footer should be:

sum({@amount},{@acctnames}) % sum({@Revrev})

...where {@acctnames} is the name of the formula that results in: Revenue, COS, SGA, Other.

-LB

 

[sandora]

Works great. You are a geeeenius. Thank you, thank you!

 

[sandora]

I know you must be getting tired of me by now but I really do learn quite a lot in between these questions. Now I'm trying to get the report title to put the correct store name in for the parameter {?store}: they have the option to choose all of them for a combined statement. That is the one that is not working. If they only choose one store it works fine. It's that multiple thing that is whacking it out.

Here is what I have:

if {GL_Account.GL Account Number} like "****-11" then "OKC" else

if {GL_Account.GL Account Number} like "****-12" then "Tulsa" else

if {GL_Account.GL Account Number} like "****-13" then "Ohio" else

if {GL_Account.GL Account Number} like "****-15" then "Florida" else

"Oval-S Combined

 

[lbass]

If "Oval-S Combined" is your default when you want all records, then be sure that you have added this into the parameter options list. Then, if you are allowing multiple values, use:

join({?store},", ")

If you are allowing only single values, then you should be able to simply add {?store} into your report title formula, assuming that you have entered the default option of "Oval_S Combined" in your parameter option list. For example, a formula might look like:

"Report for "+{?Store}+ " for the period ending June 2005"

-LB

 

[sandora]

Bass, you have been so helpful but this parameter thing has me bumfuzzled. I kind of understood it in the old version but V11 is so different, I'm really not understanding what I'm doing.

I have two parameters ?store and ?period

?store
Name: store Type: String Static
Value field: Profit Center
Value: b Description: OKC
c Tulsa
d Ohio
e Florida
Description only = T
Default =
Custom = T
Multiple = T
Discrete = T
Range = F

?Period
Name: Period Type: Date Static
Value: 1/1/05
2/1/05
etc to 1/1/06
Desc only = F
Default =
Custom = T
Multiple = F
Discrete = F
Range = T

What I am trying to do is pull the report for a chosen store for a chosen month or all of the stores for a chosen month. I'm sure there is a better way to do this. I was thinking there was a way to chose a month instead of a date range. I'm not sure where to even start with this new version.

 

[lbass]

I'm unclear what the issue is. Are your parameters working correctly with the current setup?

If you want to use a particular month, you could create a SQL expression
{%month}:

{fn Month(Table.`Date`)}

Then in the record selection formula use:

{?Month} = {%Month}

where {?Month} is a discrete number parameter.

-LB

 

[sandora]

They do work correctly as far as giving me the correct data. I'm unclear about where and how to get the Store Names and Dates in the title. I can get the ones that have only one store, but the date doesn's show up. If I choose more than one store, it only shows the first one in the title and still, no date. For title using:

Income Statement for {?Store} for {?Period}

 

[lbass]

Try:

"Income Statement for "+join({?Store},", ")+" for "+
totext(minimum({?Period}),"MM/dd/yyyy")+" to "+
totext(maximum({?Period}),"MM/dd/yyyy")

-LB

 

[sandora]

Almost there, for {?Store}, it returns B,C,D,E instead of the actual Store name in the description.