9 Replies Latest reply: Sep 28, 2010 2:20 PM by Les Backman RSS

    Table connections and if statements


      Wondering if someone can help me here. My application is built to analyze sales transactions. All of my data is brought in from one large excel file, and then connected to smaller excel files.

      Here is my issue: Each of my transactions have two salesman. Labelled as SLM-1 and SLM-2. If "John" is involved with the sale, his salesman # can appear in either the SLM-1 field or the SLM-2 field. The sales amount on each transaction is also split (not evenly) through the fields SLM-1 Amount and SLM-2 Amount. ALL of these fields are in one table.

      If I want to do an analysis of John's sales, I would have to add up all the SLM-1 Amount where John is SLM-1 AND all the SLM-2 Amount, where John is SLM-2.

      Right now I have created a table that is not connected to everything else that has each salesman name (NAME) and there salesman # (SLM). To do my analysis, on the frontend of my charts expressions, I was using =sum ( if ( SLM = [SLM-1] , [SLM-1 AMT] , '')) + sum ( if ( SLM = [SLM-2] , [SLM-2 AMT] , ''))

      This works however I have just purchased document licenses and they do not work when you have unconnected tables. So does anyone have an idea for how I can make this work?