Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Questions on pivtbl calc fields/items (XP)

    Hi.

    Can anybody tell me:

    1. Why in the attached sample.xls, the calculated field 'Field1' always returns 0.
    2. Why, when I click on cell B9 and then click on pivottable toolbar-Formula->calculated item, I can

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Questions on pivtbl calc fields/items (XP)

    1) Formulas for calculated fields operate on the sum of the underlying data for any fields in the formula NOT on the individual items. So it SUMS the "TYPE" that meet the criteria and then calculates. SInce the sum = "0", it DOES NOT equal "Long" so it returns the FALSE entry = 0.

    WHy don't you just add the FIELD "Type" into the data section and make it a "Count of Type". It will tell you how many are Long in the long rows and how many are Short in the Short rows.

    2) I am not sure on this. I was able to do it.
    Formuolas - Calculated field
    Select T TIme
    <insert Field>
    Add (No quotes)"/2"
    <add>
    <ok>

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Questions on pivtbl calc fields/items (XP)

    Thanks for your response, Steve.

    re 1: I want to display one value if the "Type" field is "Long", and another if it is "Short". So I'm not sure the count of the different types will be able to get me there.

    re 2: did you try "calculated item" instead of "calculated field". That's where I run into a problem.

    Best regards,

    Dan

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Questions on pivtbl calc fields/items (XP)

    re re1) If your calc would have worked it would have displayed the Count of TYPE for the LONG rows and o for the short rows. Are you trying to display a 1 if the row is a Long row and 0 if a short row. You could add your calculation into the table and then add this new column as a field next to the type column.


    T Time is a FIELD, it is NOT an ITEM (it does NOT have items so you CAN NOT add an item from it). It is a DATA field. If you put the field in the ROW or COLUMN it will have items (each of the unique values in the column) so you could then ADD ITEMS, but you want to add the FIELD so you need to use ADD FIELD not ADD ITEM.

    Fields are the columns of Info (eg TYPE) is a field. Items in TYPE are SHORT and LONG.


    Steve

  5. #5
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Questions on pivtbl calc fields/items (XP)

    Steve,

    I think I understand what you're saying about the Calculated Item vs. Calculated Field. However, I'm unclear about the logic you're proposing about (1). If I put a Count of Type in the data area, why would it display 0 for "SHORT" rows? Maybe I'm missing something, but it seems like sometimes it would, but sometimes it wouldn't.

    Dan

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Questions on pivtbl calc fields/items (XP)

    No. if you add count of type in the "long rows" it will count how many longs and in the short it will count how many rows.

    As I mentioned IF your calc HAD worked in the long rows it would have displayed the count of the longs (this is identical to count of TYPE). but yield a zero for the rows with short. and I ask again: Is that what you want to do?
    If so, you can acoomplish this, by adding a column to your datatable source (NOT the Pivot table) with the calculation you tried to add as a field. This will act on individual rows (where the calc field will not!).

    Then ADD this field to the DATA in the wizard to your pivot table.

    If that is NOT what you want, What do you want displayed in this column? If you want a 1 for Long and 0, for short in a column, then add the field as ROW field.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •