Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Avoid circular conflict

  1. #1
    Board Regular
    Join Date
    May 2010
    Location
    St Albans, Herts
    Posts
    381
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Avoid circular conflict


    Avoid circular conflict
    I am trying to to use data in one column in a second column, but running into a circular arguement. Is there any way to avoid this. My table looks like this

    PINVNo Customer No Service Code Line Type Code Service
    PINV000002 2000003 10000 Item 501720169 LUBE01
    PINV000002 2000003 10000 Service Code LUBE01 LUBE01
    PINV000002 2000003 10000 Item ENOCAS012B1 LUBE01
    PINV000002 2000003 20000 Item MOTSLOT MOT01
    PINV000002 2000003 20000 Service Code MOT01 MOT01
    PINV000002 2000003 30000 Service Code SUP01 SUP01

    I am trying to calculate the last column from the 5th column. The lines 10000 are all part of the service LUBE01, but the types are for parts (items) and the sale cost (service code). I need to group these together for other calculations.

    The code I tried is this

    [CODE]=IF( [Type]="Service code",
    [Type],
    CALCULATE(
    VALUES('Posted Service Invoice Line'
    Code:
    ),
                               FILTER('Posted Service Invoice Line',
                                             [PINVNo]=EARLIER([PINVNo]) &&
                                             [Service Code Line]=EARLIER([Service Code Line]) &&
                                             [Type]="Service Code"
                                             )
                                )   
          )
    I thought the IF clause would exclude any rows where the circular argument would happen. Is there a way to write this please?

  2. #2
    Board Regular
    Join Date
    Dec 2016
    Location
    Mexico
    Posts
    178
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Avoid circular conflict

    Using CALCULATE in Calculated Columns can cause circular dependencies as Alberto Ferrari and Gerhard Brueckl describe in their blog posts. Try reading them and you will probably find your answer.
    FranzV

    Enjoy Excel

    Please give me some feedback. A like will do, but a reply will always be welcomed.

    Special thanks to The Italians, Imke Feldmann, Rob Collie, Bill Jelen, Debra Dalgeish, Chandoo, Ken Puls, Chris Webb, Matt Allington, Kasper De Jong, Chris Newman, and so many others that generously share their talent and guide us in finding solutions with Excel.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com