1 Reply Latest reply on May 15, 2013 5:01 AM by Jim Wahl

    Simple query re: calculated fields that take into account date filters

    Ajay Arora

      Hello,

       

      Would be very grateful if anyone could help me with the following.  I suspect there's a very simple solution but I've just not been able to get to it.

       

      Summary:  Using Tableau 8.0.  Have two excel files linked together.  1) Target.xls which shows a sales persons targets 2) Achievement.xls which shows a sales persons achievement

       

      The target file contains the following fields...So far it has three months worth of Data - so the month can be April, May or June.

       

      Salesperson:   Mr Smith

      Month: April

      Product Name: Product XYZ

      Target Value:  5000 USD

       

      The achievement file has a different field structure as it's pulled from a different database.  But the upshot is the Salesperson/Month/Product name fields are in common - so I link using those fields.

       

      Now I've managed to link the files.  And I also am able to drag and drop my fields to get the tables I want - with the numbers correctly popluated.  So for example a table that looks like this

       

       

      MONTH   SALESPERSON PRODUCT NAME              TARGET    ACHIVEMENT             CALCULATED FIELD WANTED HERE

       

      April 2013          Mr Smith              Product 1              5000 USD     8000 USD

      April 2013         Mr Smith               Product 2              3000 USD      2000 USD

      May 2013        Mr Smith                Product 1              3000 USD      1000 USD

      May 2013      Mr Smith                  Product 2              4000 USD       9000 USD       

       

       

      Now here's where I get stuck.  I want to use a calculated field to add another column to the extreme right - which will  simply work out the target vs achievement percentage.

       

      In the calculated field I use the formula [ACHIEVEMENT]/[TARGET]

       

      Now this does give me some values in the table - but they are incorrect.  Comes out with strange percentages.  I think the reason is because the formula uses all the cumulative target values and the cumulative achievement values - i.e. it doesn't take into account my month column - and so not dividing the correct numbers.

       

      So how do I get the calculated formula to acknowledge the existence of the month column in the table - and give me the correct percentages accordingly?

       

      Any assistance would be much appreciated. 

       

      Thanks in advance.