5 Replies Latest reply on Aug 20, 2018 3:50 AM by Jose Daniel Moreira

# Calculated Field with different field dates

Good morning
I´m a relatively new user to Tableau.
Hopefully I do follow correctly the rules in the community.

Right now, I´m stuck with what it seems to be a simple calculation, but haven´t been able to get the right answer.
I´ll Attach the Workbook so you guys can help me out.

Data comes from 2 different data source (MySQL and Google Sheet). They are Joined by the field "Source"

Objective:
Dividing Income by Request by month.

Problem:

It seems I have 2 different dates: As I understand one would be Agreggate and the other would non-agregate and this

could be this reason why Im not getting the answer, you can easily see data will change  that by changing the pill "Date"

(from Google sheet) by "Req Created date" (from My SQL) in the Income x source sheet column.

So, What I need is a simple division, but we now in Tableau sometime this is not an easy task.

Divided by

As you see the idea is dividing the resulting number from Income by Request.

I used this formula.

{FIXED DATEPART("month",[Date]),[Source1],[Product1]: MIN([Book Income])}

/{FIXED DATEPART("month",[Req Created Date]),[Source1],[Product1]: COUNTD([Req User Id])}

As you can see, no luck here. The result inJanuary should be 9210/5537 = 1.66

I would be really appreciated.
Thanks very much

• ###### 1. Re: Calculated Field with different field dates

Hi,

I didn't find any mistake in your calculation, however you just need to use aggregate function on your calculated measure like this

Use MIN() on your existing calculation

Hope this will help.

Mahfooj

1 of 1 people found this helpful
• ###### 2. Re: Calculated Field with different field dates

HI Mahfooj,

OMG! I can´t not believe you got it with the same formula. First thanks very much for replying. I´m missing something because
I´m as you suggested using the MIN function in the Calculation.  On the other hand I tried to use the
SUM fuction on both sides because makes a lot of sense, but no luck.

Did you change something in the Calculated Field?
Thanks for the time and the help

BR

• ###### 3. Re: Calculated Field with different field dates

Earlier I tried with SUM() but that was not giving the correct result so I edited my reply.

Here I've used your formula and include [Name Orgn Ctgrs] dimension in the formula and using MIN() aggregation.

[Name Orgn Ctgrs] field is optional though I included because you've used it in your view. So that results gets computed properly.

Hope this will help.

Mahfooj

1 of 1 people found this helpful
• ###### 4. Re: Calculated Field with different field dates

Efectively Mahfooj,

Adding the [Name Orgn Ctgrs] dimension to the formula was the solution.
Thanks very much. I marked the answer as correct

Can I ask one more thing, Is regarding the display of the information. As you can see I have
"Income Source", this is displaying by a MIN calculation, because its a FIXED table, to display the correct number I have to add "Product" and "Source" dimensions, otherwise will display the MIN number of a lower

level of agregation, so it will exclude a lot of numbers in the calculation.

My question is: How can I display the result of the SUM of a MIN calculation. In this case I want to show
the SUM of "Book Income" of the "Name Orgn Ctgrs". Example:

Hopefully you got the idea of the question.
And again, thank you very much for the help.

BR

• ###### 5. Re: Calculated Field with different field dates

I get the result using the same formula, and allowed me to go to different detail level.

{FIXED DATEPART ("month", [Date]), [Source], [Product], [Name Orgn Ctgrs] : MIN (Book Income)]}