14 Replies Latest reply on Jan 8, 2019 3:02 PM by Michael Gillespie

    IF/Then formula for a Date?

    Michael Kaminski

      I am probably making this question a lot more confusing than I need to.  But, I am trying to create a list of my clients that received service in 2018. [Program Start Date] and [Program End Date] are date fields in the database.  So, I'd like to have a list of all of my clients who have start dates in 2018 or end dates in 2018. Not start and end dates, I already did this by easily filtering those previously mentioned fields. Rather, I also want to see start or end dates in 2018. Like someone who had service in December of 2017, received service in 2018, but has no end date in 2018.  I am guessing I need to create a new calculated field, to filter my clients by? How would one do this?  Thanks in advance for any help.

        • 1. Re: IF/Then formula for a Date?
          Alan Perez

          you can create a cohort graphic, where you can see the clients that had service in 2017 and also in 2018, this can be done with calculated field as you mention it

           

          If you have a small data set that you share with me in excel, I can help you

           

          regards

          • 2. Re: IF/Then formula for a Date?
            Michael Kaminski

            Hello Alan,

             

            I attached an Excel spread sheet.  I included all ‘program start dates’ and only the 2018 and 2019 ‘program end dates’.  (2019 program end dates are the “Projected” ends for the customer.)

             

            Thanks for your help.

             

            Mike

            • 3. Re: IF/Then formula for a Date?
              Michael Kaminski

              Hi again Alan,

               

              One more question for you, why doesn’t this work in Tableau? See below:

               

              IF  >= 1/1/2018 OR <= 12/31/2018

              THEN

              END

               

              Or

               

               

              WHEN ( >= 1/1/2018

               

              AND  <= 12/31/2018)

               

              OR ( >= 1/1/2018

               

              AND  <= 12/31/2018) OR ( = 'Null')

              END

              • 4. Re: IF/Then formula for a Date?
                Michael Gillespie

                Because that's not Tableau's syntax for calculated fields.  Tableau doesn't have a WHEN operator, and you have to specify the field name you're testing your dates against.

                 

                So, something like:

                IF [StartDate]>='1/1/2018'

                OR [EndDate]<='12/31/2018'

                THEN 'OK'

                ELSE 'Not OK'

                END

                • 5. Re: IF/Then formula for a Date?
                  Michael Kaminski

                  Yes, I specified the field name in my email back to Alan, I am not sure why it didn't show up here in the forum. Yes, I am starting to figure out what syntax is only SQL and only Tableau.  I want to just type in SQL, but whenever I see, or figure out the Tableau syntax, it makes sense because of the ooey-gooey filtering abilities that the Tableau software is able to perform.

                   

                  I'll try your formula recommendation. Thanks.

                  • 6. Re: IF/Then formula for a Date?
                    Michael Gillespie

                    Ah, forum formatting issues.  Not uncommon!

                     

                    There are places in Tableau where you CAN write SQL, but that isn't one of them!

                     

                    Don't interpret the single-quote stuff literally: it all depends on the specifics of how your data is formatted.  You might only need to specify that either of those two fields contains "2018", for example.

                    • 7. Re: IF/Then formula for a Date?
                      Michael Kaminski

                      I am getting an error with your syntax?  It doesn’t like me using date and string data together.

                      • 8. Re: IF/Then formula for a Date?
                        Michael Gillespie

                        Your date fields in the Excel file are actually text fields.  From the error you report, I assume you cast them as real Dates in Tableau.  That's what I meant about the single quotes around the '1/1/2018'.  That has to be a Date field as well.

                         

                        Try it like this:

                         

                        IF [StartDate]>=Makedate(2018,1,1)

                        OR [EndDate]<=Makedate(2018,12,31)

                        THEN 'OK'

                        ELSE 'Not OK'

                        END

                        • 9. Re: IF/Then formula for a Date?
                          Michael Kaminski

                          Yes, in the database and Tableau, names are strings and the dates are dates. I’m not sure what you mean by using Makedate? MAKEDATE is an unknown function by me and Tableau.

                          • 10. Re: IF/Then formula for a Date?
                            Michael Gillespie

                            Ok, version issues.  What version of desktop are you on?  makedate() was introduced a while ago but you may be on a previous version.

                             

                            In the Excel file you attached, the dates are TEXT values: they come across as '1/1/2018 (single quote in the cell).  When I open the spreadsheet in Tableau, it sees those 2 columns as STRING values, not Dates.  I can change the datatype to Date, but that's not how they come in natively.

                             

                            If you want to leave them as strings, you could do CONTAINS([StartDate],'2018') (for instance) instead of the greater/lesser than stuff.

                             

                            But if you need to use the dates as actual dates somewhere in the analysis, that won't be very helpful.

                             

                            You could try DATEPARSE - syntax is different but you can figure it out, I'm sure!

                            • 11. Re: IF/Then formula for a Date?
                              Michael Kaminski

                              I am using version 2018.2, and the MAKEDATE function is not recognized.  And the Excel file, was a Tableau worksheet export that I created for the other gentleman, Allan, so he could examine an example of the data I was looking at.  He talked about creating a “Cohort graphic”?

                               

                              Anyways, I am working with a string field and 2 date fields in Tableau Enterprise 2018.2.  Talking to one of our end users, and thinking more about it on my own, I am thinking I can answer this formula question by using the software filters.  Because, what we are trying to write out in code is the same as what the software’s filters are giving you the ability to do – select whatever specific dates that you want, from whatever specific field you want, and you can display the names (associated with your chosen dates) by simply adding the “Names” field to the rows shelf.

                               

                              So, the more I dive into the coding aspect, the more I am thinking that I don’t need to stress my brain in this way.  Maybe, I just need to let the filters do the work.

                              • 12. Re: IF/Then formula for a Date?
                                Michael Gillespie

                                Making more sense now.  Some root data sources don't support MAKEDATE; Excel does, so that's where the disconnect is. What is the ultimate (non-Excel) data source?

                                 

                                You are mostly right about filters vs. calculations.  In this case, you're looking for an OR, and that can be tricky if all you have to work with is 2 column filters  Same as Excel: if you filter a column, then you potentially lose data you want from a different column.

                                 

                                So, the calculation is likely to be what you want.  it's just a matter of getting the syntax right when you're doing the comparison.

                                • 13. Re: IF/Then formula for a Date?
                                  Michael Kaminski

                                  For this particular report, the data source is a Microsoft SQL database.  (We currently use 2 Microsoft SQL databases and 2 MySQL databases) At least it is for right now, we are in the middle of a data warehouse construction project.

                                   

                                  You are right – “OR” is tricky. Especially having 2 columns to filter and wondering if the filtering on the first column will take data away from the second column.  So, why filter my first date column (Start Date)? Use it all!  It’s not hurting anything. It’s making every client name in our database, available, if called on by the by the second date column (End Date).  I will just filter for the 2018 end dates and the Null end dates so every name that has a start date can be chosen from.  That’s what I am thinking.

                                  • 14. Re: IF/Then formula for a Date?
                                    Michael Gillespie

                                    Yep, that may just work.  much depends on the specifics of your data but try it and if it does work, you're done!