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
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.
Export_010819_2018 Clients.xlsx 57.3 KB
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
WHEN ( >= 1/1/2018
AND <= 12/31/2018)
OR ( >= 1/1/2018
AND <= 12/31/2018) OR ( = 'Null')
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:
ELSE 'Not OK'
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.
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.
I am getting an error with your syntax? It doesn’t like me using date and string data together.
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:
ELSE 'Not OK'
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.
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!
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.
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.
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.
Yep, that may just work. much depends on the specifics of your data but try it and if it does work, you're done!