2 Replies Latest reply on Jul 20, 2017 10:33 AM by Patrick Williams

    Count # of growth periods

    Patrick Williams

      All -

      I would like to take daily stock price data, and count the distinct days in which the price increased relative to the previous day. I began by using the "lookup" function to bring the previous day value forward, but then I was not able to properly construct a conditional statement to count the days in which the stock price grew.

       

      Here is a simplified version of raw data:

      07182017 Stock Price Raw Data.PNG

       

      And this is what I want to achieve:

      07182017 Stock Prices Desired State.PNG

       

      Attached is my workbook in its current form. I feel like there is an opportunity for Level of Detail calculation, but LoD does not like table calculations (which I feel like is the only way to bring the "Previous Value" forward, so I am getting tripped-up...

       

      Thank you for your time  & consideration.

        • 1. Re: Count # of growth periods
          Jim Dehner

          Hi Patrick

          This is a brute force way of accomplishing the goal but here goes

           

          first I created a field that gave me a 1 on up days

               if (MAX([Closing Price]) - [Previous Value])>0 then 1 end

          Then

          if isnull(["UP Day Indicator"]) then 0

          else ["UP Day Indicator"]+PREVIOUS_VALUE(["UP Day Indicator"])

          end

           

           

          when added to your viz they produce this

           

          I am on a different version of tableau and you would not be able to open the workbook

           

          Let me know if this helped

          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: Count # of growth periods
            Patrick Williams

            Thanks Jim, but not quite what I am going for. In your calc it looks like the count is restarting - the correct count is 5 days, your viz shows 3.  I tried a similar construct, but instead of returning 1 / 0 , I tried returning the date value then I was going to just count the distinct dates, but that doesnt seem to work.

             

            if MAX([Close]) - ([Previous ( CLOSE )]) > 0

            THEN COUNTD([String (Date)])

            ELSE NULL

            END