Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Feb 2005
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem with item formula in pivot table (2002)

    I tried searching the posts first but couldn't find one that addressed my problem, which is:
    I created a item formula that gives me net sales by store but when i try to use the formula at the sales person level it breaks the sort order and lists every rep from every store at each store but onl;y shows data for the reps actually from that store. I am sorry i cant post an example as the data is proprietary but if i need to create a bogus data set to illustrate my problem please let me know and i will work on that. I read an issue in a reference guide that mentioned you may need to turn off grand totals with item formulas to prevent duplication of data and i THINK i did this on the table options menu but may need to turn off more than Grand Totals?
    Thank you i hope this post makes sense to someone!

  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: Problem with item formula in pivot table (2002)

    I am not clear what you want or what you are trying to describe.

    If you could post a simple example data set with and describe what you want, I think it would help us to solve the problem.

    Steve

  3. #3
    New Lounger
    Join Date
    Feb 2005
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with item formula in pivot table (2002)

    Ok i have some test data to show you. If you remove net sales from the qty drop down menu each company_nm only has the srep associated with that company but when i put the formula in it repeats every srep for every company but at least it only has data for the srep that goes to that company.

  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: Problem with item formula in pivot table (2002)

    What arre you trying to create? if you want the formula you created to calculate on only the visible items in the pivot table, you can remove the item formula and only the names with data in the company will be shown, and then add the formula (outside the pivot) in cell G3:
    <pre>=-D3+E3+F3*2</pre>


    Or even:
    <pre>=SUMPRODUCT($D$4:$F$4,D5:F5)</pre>


    and copy it down all the rows...

    By adding an "item formula" you are adding the item to everything, adding more entries than you want...

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with item formula in pivot table (2002)

    In addition to Steve's recommendation, you could add a column to your source data to calculate the net sales amount in your data table, then add that field to the pivot table.

  6. #6
    New Lounger
    Join Date
    Feb 2005
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with item formula in pivot table (2002)

    I tried calculating outside the pivot table but if someone wants to just see their store or a single srep the formulas don't work because the cells don't stay in the same locations breaking the formula.

  7. #7
    New Lounger
    Join Date
    Feb 2005
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with item formula in pivot table (2002)

    Ban to add the formula to my source data is beyond me the data i attached was 1 day but the data i need to work with is month to date 12 days so far and to create a formula it would have to search the entire data source for each rep and each transaction type and each qty.

  8. #8
    New Lounger
    Join Date
    Feb 2005
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with item formula in pivot table (2002)

    What do either of you think of trying to manually group the sreps by store then turn on the formula would i be able show only the grouped data and keep the by store sort intact?

  9. #9
    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: Problem with item formula in pivot table (2002)

    I don't understand what you mean. The formulas are tied to the rows...

    Steve

  10. #10
    New Lounger
    Join Date
    Feb 2005
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with item formula in pivot table (2002)

    Not when you pick just 1 store or one rep the formulas stay in the rows the are in and reference the same cells that may or may not be displayed any longer.

  11. #11
    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: Problem with item formula in pivot table (2002)

    <P ID="edit" class=small>(Edited by sdckapr on 13-Feb-05 18:49. corrected formulas)</P>The formulas stay in the rows that are not visible. If you only want to "Hide them use instead:

    =IF(COUNTA(A5:F5)=0,"",-D5+E5+2*F5)

    or:
    =IF(COUNTA(A5:F5)=0,"",SUMPRODUCT($D$4:$F$4,D5:F5) )

    and copy it down the rows.

    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
  •