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



      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





      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.