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

    Calculated Field with different field dates

    Jose Daniel Moreira

      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

       

      If anyone can Help me or guide me, also share some reading material about this topic.
      I would be really appreciated.
      Thanks very much

        • 1. Re: Calculated Field with different field dates
          Mahfooj Khan

          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
            Jose Daniel Moreira

            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
              Mahfooj Khan

              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
                Jose Daniel Moreira

                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
                  Jose Daniel Moreira

                  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)]}