8 Replies Latest reply on Aug 12, 2015 5:56 AM by Rody Zakovich Branched to a new discussion.

    finding the date

    Christiana Won

      Hello.

      So here is m y question:

      Let's say I set some initial date, say 7.1.1957,

      and from that date, there's some interval, let's say 4.1.1959

      Then within that interval, I want to be able to find the the date when the "close" no longer goes higher than the "close" of the initial date that we already set.

      In other words, I would like to find the last date where the "close" is equal or higher than the close of the initial date (in this case 7.1.1957's "close") within the interval (7.1.1957~ 4.1.1959).

       

      I want to be able to set the initial date and the last day or the interval as a parameter and be able to freely change them around.

        • 1. Re: finding the date
          Rody Zakovich

          Hello Christina,

           

          Quick question. Are you trying to find the Last Date is Equal to or Higher within the initial sequence, i.e.

           

          8-11-2015 4-59-15 PM.png

           

          Or the MAX date across the entire set where the Close is higher than the initial close?

           

          8-11-2015 5-01-00 PM.png

           

          Best regards,

          Rody

          • 2. Re: finding the date
            Christiana Won

            the first one

            so within the sequence.

            Thanks for the help

            • 3. Re: finding the date
              Rody Zakovich

              Hmmm, this is a little difficult.

               

              To get you started, I created two Parameters - One for the Initial Date and One for the End Date.

               

              Then I created a calculated field like this using an LoD

               

              IF [Close] >= { MAX(IF [Date] = [Initial Date] THEN [Close] END) } THEN [Date] END

               

              This returns the Value of the Initial Date on a Row Level. This way we can do some comparisons.

               

              I am going to have to play around with this more, and incorporate some Table Calcs to find the LAST date within the first sequence.

               

              Regards,

              Rody

              • 4. Re: finding the date
                Rody Zakovich

                Ok, here is what I got....

                 

                I created another calculated field like so....

                 

                IF MIN([Date]) <= WINDOW_MIN(IF ISNULL(LOOKUP(MAX([LoD to Greater than Initial Close]), 1)) THEN MIN([Date]) END)

                THEN 1

                ELSE 0

                END

                 

                This finds the MIN date where the NEXT date in the partition is NULL.

                 

                I then place it onto the Filter shelf and drag to only show 1.

                 

                8-11-2015 5-31-00 PM.png

                 

                Please find the attached workbook.

                 

                Hope this helps

                 

                Rody

                 

                Simon Runc the method I came up with works, but I feel like there is maybe a simpler way to do this...what are your thoughts?

                • 5. Re: finding the date
                  Christiana Won

                  I think it works.

                  One more question.

                  Do you know how I would count the number of days until it no longer goes higher than the initial date?

                  so in this case from july 1st through aug. 2.

                  • 6. Re: finding the date
                    Rody Zakovich

                    Yeah, you can do that with a calc like this

                     

                    WINDOW_SUM(COUNTD([Date]), FIRST(), 0)

                     

                    If you place that onto your Viz, it will effectively be a "Running Count".

                     

                    I added a second worksheet to show you how it works while only showing the Last date in the sequence.

                     

                    Let me know if you have any more questions.

                     

                    Regards,

                    Rody

                    • 7. Re: finding the date
                      Simon Runc

                      hi Rody,

                       

                      You are certainly putting those LoD's through their paces!!

                       

                      ...I've had a bit of a play with this, and can't simplify what you've done here. I'd say each of the fields/filtering is necessary to get the desired result. There are variations on your logic using PREVIOUS but I actually think that makes it more complicated not less.

                       

                      I think in general with these 'Excelly' calculations (as I'm calling them!! due to the fact in Excel you can reference any cell from any cell, so can break data-structuring rules to achieve almost any type of calculation*)...Tableau isn't really designed to do these, although (as you've shown many times) anything is possible, so a final solution will look 'hacky'.

                       

                      *I would add...I'm not advocating Excel here, as the maintenance of these 'structure breaking' calculations is a very manual affair. The solutions to these problems in Tableau, although tricky to set up will work for all data....In Excel it is often someone's job to spend Monday manually re-setting up these kind of calculations, as they often need to be redone each time the data updates...Data Philosophy Rant over!!

                       

                      Nice work though

                      • 8. Re: finding the date
                        Rody Zakovich

                        Hi Simon,

                         

                        Thank you, as always, for jumping in and giving your thoughts.

                         

                        Luckily, it was V9, which simplified this "a little". This could have been done with V8.3, using Table Calcs, but setting up the addressing and computing for that would have been a nightmare!

                         

                        When I started my career a year ago, I was fortunate to start with Tableau, so I never had to learn more than basic Excel functions. But I take that as a blessing, because I never developed any habits that would be counter-productive in Tableau (Like 'Structure breaking' or the dreaded 3D charts.......). So I completely agree with your statements here.

                         

                        One of the biggest challenges I have at work is moving people away from the Excel mindset. People want to see Crosstabs sooooo bad. I guess when you have been doing something for 10+ years, it's hard to see how much greener the grass is on the other side.

                         

                        Ok enough for my rant

                         

                        Thanks again,

                        Rody