5 Replies Latest reply on Apr 6, 2018 1:57 PM by Chris Ruth

    Dynamically Calculate Value

    Chris Ruth

      Hello -

       

      I have three columns :

            

                     1. Date

                     2. Count

                     3. Engaged Count

       

      I have a dashboard with a date slider (the user can dynamically choose Final Date and Initial Date)

       

      let X = (Engaged Count @ Final Date) - (Engaged Count @ Initial Date)

      let Y = (Count @ Final Date) - (Count @ Initial Date)

       

      I would like to calculate DIV(X*100, Y) -- which would be a single scalar value -- on the fly, as the user changes the date range in the dashboard.

       

      Note: Engaged Count is always less than Count, hence why I need to multiply by 100 within the DIV function .

       

      Best,

      Chris

        • 1. Re: Dynamically Calculate Value
          Jim Dehner

          Chris

          Not certain what your expectation is because count always exceeds engaged count and DIV will return 0 with the formula as written

           

          if you wanted DIV( [COUNT],[ENGAGED COUNT])

          then the result would plot out as

           

           

          see attached

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Dynamically Calculate Value
            Chris Ruth

            Hi Jim, thanks so much for the quick reply -- I have edited my question to clarify.

            • 3. Re: Dynamically Calculate Value
              Jim Dehner

              thanks for the clarification

               

              see the attached

              the formula I used is

               

               

              it returns this

               

               

               

              Jim

               

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

              • 4. Re: Dynamically Calculate Value
                Joe Oppelt

                In the attached I created calcs to tell you what the smallest selected date is, and what the largest is.

                 

                You are missing data between days 6 and 23, so if you pick 10 as the minimum on your slider, 23 is actually the minimum date returned.  That's the best Tableau can detect for you.

                 

                Given that, I have one calc that tells you the engaged count at the minimum date.  ([Engaged at initial].)

                 

                You can model off that to do the same for engaged at end, and the same for count at initial and end.


                See attached.

                • 5. Re: Dynamically Calculate Value
                  Chris Ruth

                  Hi Jim,

                   

                  Yes - this is what I am looking for. However, "the value you want" is not accurate - If I select Day 1 - Day 10, I should theoretically get:

                   

                  X = (23 - 1) = 22

                  Y = (70 - 16) = 54

                   

                  X*100 / Y = 22*100/54 ~ 40

                   

                  But I get 31.

                   

                  I do seriously appreciate your help.

                   

                  Chris