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

tableaucommunity@tableau.com — Wed, 15 May 2013 12:01:48 GMT

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.

Really simple division calculation via calculated fields - but it just won't work! Please help.

tableaucommunity@tableau.com — Fri, 12 Oct 2012 17:29:07 GMT

Hello All,

Tableau newbie here - love the product!

However having a bit of a strange problem with a really simple calculated field calculation - would appreciate any assistance.

So data source is an excel file - and I used the fields of HQ Code, POB, PC and Date to construct the above table in the BI.

HQ Code basically refers to a particular salesperson. POB refers to the value of sales. and PC refers to productive calls.

So drag the relevant fields to make that table upto PC.

Then now that I have my monthly POB and monthly PC - what I wanted to work out was the average sale per call - for each of the months.

In other words a calculated field formula of [POB]/[PC]

However as you'll see from the above .jpg - the number I get in the output is wrong. So for example AP-01 has a July POB of 53,233. And a July pob of 139. So the figure should be 53233/129 = 382. Instead I get this strange value of 7804 as you can see from the .jpg

Here are things I've tried so far to get it to work.

-Double checking that the raw data in excel is in "number" cell format.

-Noticed that the field was described as "float" in the calculated field bit. So revised the formula to be integer instead i.e. something along the lines of (INT[POB)/(INT[PC]) or whatever. The number changes slightly but completely the wrong order of magnitude.

Anyone able to help with what the formula should be? Dividing two numbers should be really easy - am confused.

Does this have anything to do with the monthly breakdown - i.e. calculated fields is not able to pick up the date field I've got going on in the table?

Any assistance would be much appreciated.

Thanks in advance

Content in Tableau Community Forums

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

Really simple division calculation via calculated fields - but it just won't work! Please help.