4 Replies Latest reply on Nov 2, 2016 9:51 AM by Michel Caissie

    Functional Parameters in (%) and ($)

    Irvin Palacios

      Hello community,

       

      I feel like this should't be this hard, but here goes.

       

      I would like to show increases and decreases in my values using the parameters in my workbook. I am successful in making the parameters where % changes happen, but I am unsuccessful when it comes to making changes with the dollar ($) amounts. I would like to have the functionality of doing both in the same worksheet.

       

      Here is a screen shot of my calculated field, but workbook is attached as well.

       

      Calculated Field.png

       

      If I could have suggestions to this, that would be amazing.

       

      Ideally I would just like to divide the [Income] by the number of records that fall into 'One-Time' given 'Income' and that would solve my issues, but I don't know how to approach this problem.

       

      Sample workbook is attached.

        • 1. Re: Functional Parameters in (%) and ($)
          Michel Caissie

          Irvin,

           

          you can use lod to compute the Number of records.  Something like ;

          {SUM( if [Activity] = 'Income' and [Frequency] = 'One-Time' and [Amount] <= [Income]  then  [Number of Records] end )}

           

          I'll let you check the logic, don't know if you want to use Amount here or Adjusted Amount. (didn't understand why you have negative amount, see sheet 5).

           

           

          Next you can replace the Number Of Records of your calculation with these new calculations.

          CASE [Activity]

              WHEN 'Income' THEN (IF [Frequency] = 'Recurring' THEN [Adjusted Amounts] + ([Recurring Income (%)]*[Adjusted Amounts])

                                  ELSEIF [Frequency] = 'One-Time' THEN [Adjusted Amounts] + ZN( ([Income]/[IncomeNbrRec]) )

                                  ELSE [Adjusted Amounts]

                                  END)

              WHEN 'Expense' THEN (IF [Frequency] = 'Recurring' THEN [Adjusted Amounts] + ([Recurring Expense (%)]*[Adjusted Amounts])

                                  ELSEIF [Frequency] = 'One-Time' THEN [Adjusted Amounts] - ZN( ([Expense]/[ExpenseNbrRec]) )

                                  ELSE [Adjusted Amounts]

                                  END)

          END

           

          Michel

          1 of 1 people found this helpful
          • 2. Re: Functional Parameters in (%) and ($)
            Irvin Palacios

            Michel! You're the best!

             

            Thanks this was perfect.

             

            I did have to make one minor change to the [IncomeNbrRec] and [ExpenseNbrRec], there was just one to many criteria for what I needed. But other than that it was perfect!

             

            I had to take out: "and [Amount] <= [Income]" out of both of them...

             

            The [Income] parameter is meant to have the user add or subtract their desired amount from the "One-Time" amount.

             

            Can't thank you enough!

             

            -Irvin

            • 3. Re: Functional Parameters in (%) and ($)
              Irvin Palacios

              Hello Michel,

               

              May I ask one more question. I was thinking about the solution you provided and I wanted some clarification on why your method work.

               

              How is it your calculation is LOD? I thought that if you ever included some function, in this case "Sum", it would convert your calculated field into an aggregate? Why is it that in this case it did not?

              • 4. Re: Functional Parameters in (%) and ($)
                Michel Caissie

                Technically it`s the curly brackets {}   that defines the calculation as a LOD. And because the SUM aggregation is nested within the LOD, the calling function sees it as  a single value.

                 

                I strongly suggest this excellent blog to understand LOD.

                 

                What’s new in Tableau 9.0? Part 2 – Level of Detail Expressions | Bora Beran