11 Replies Latest reply on Nov 5, 2013 4:29 PM by Aaron Clancy

Common Beginner Problem with Creating Calculated Field

I think I'm running into a problem then I learned about in one of the tutorial videos though I can't seem to relate it to my issue precisely enough to figure out the solution.

Ultimately, I want a stacked bar chart showing the percentages that two currency fields are to the total. So I'm creating calculated fields to show the % of total figures for each field. But I can't seem to get those final % numbers (which turn into agg field (right or wrong?) into a stacked bar format. I'm new at calculated fields, so not sure if I'm doing it right. And maybe I don't even need a calculated field for what I'm trying to do...create "% retained" vs. "% unretained" and then show as a stacked bar. I can managed the first calculation to determine "retained" but the "unretained" eludes me.

Arghh....

• 1. Re: Common Beginner Problem with Creating Calculated Field

Is unretained  =   (Non-retained + retained)  minus    (Non-retained)

I have the following:

I left out some markets that had negative values because I'm not sure how you want to account for negatives.

Is this close to what you're needing?

• 2. Re: Common Beginner Problem with Creating Calculated Field

Hi Aaron,

Actually unretained  =   (Non-retained + retained)  minus (retained), and this small difference might be the hitch. But it looks like you are on to something. Could you try to generate the calculation for unretained so I can see if it works? And then maybe you can help me understand what in the world I was doing wrong to begin with.......

• 3. Re: Common Beginner Problem with Creating Calculated Field

I actually meant to type what you had sorry,

The calcs I used

Non-retained:

[FY13 AF(Retained + Non-Retained)]-[FY13 Retained Agencies]

%Non-retained

Sum ([FY13 AF Non-Retained Agencies])/sum([FY13 AF(Retained + Non-Retained)])

%Retained

sum([FY13 Retained Agencies])/sum([FY13 AF(Retained + Non-Retained)])

• 4. Re: Re: Common Beginner Problem with Creating Calculated Field

Hi Aaron -

Thanks for hanging in there with me. When I replicate your calculation for % non retained I get the following screen shot telling me that I cannot aggregate any further. I suppose it's because the first field is already a calculation?? I'm so new to this that it's mind boggling...

• 5. Re: Re: Common Beginner Problem with Creating Calculated Field

The Calc "Non-retained agencies"  can't have an aggregation in it.

In the example above I used:

[FY13 AF(Retained + Non-Retained)]-[FY13 Retained Agencies]

make sure you don't wrap a sum around any of the above.

• 6. Re: Re: Common Beginner Problem with Creating Calculated Field
Okay...I'm getting there. Do you still have the worksheet that you produced the multiple stacked bars from? I can't seem to get the bars stacked. I can get two different panes with nonretained in one and retained in the other, but no matter what I do I can't get them to stack the way you did.
• 7. Re: Re: Common Beginner Problem with Creating Calculated Field

Yes, ma'am.  Attached to my previous post :-)

• 8. Re: Common Beginner Problem with Creating Calculated Field

GASP!!

You are ADVANCED. I don't understand how you produced those stacked bars but I'm going to pick it apart until I do!

Thanks for the closure Aaron!

Joan

• 9. Re: Common Beginner Problem with Creating Calculated Field

Ha thanks, this might help

Steps:

2.  Don't use "Show Me"

3.  Put "Markets" on Columns.

4. Put measure Values on rows

5. Remove all measures from the Measure Values list that aren't the two you need.

6.  Place measure names on the color shelf

7.  Place measure values on label (or just check the "Show Mark Labels" on the Label editor)

Putting "Measure Values" on rows gives you the ability to create a stacked bar.

putting measure names on colors essentially cuts the bar up into the different measure fields that you have in your measure values list.

You can also do fun sorting stuff if you need

Hope that helps

• 10. Re: Common Beginner Problem with Creating Calculated Field

Man alive! I feel like a glutton for asking, but do you also have a recipe for that sort?

• 11. Re: Common Beginner Problem with Creating Calculated Field

No Problem

Markets>Sort