Today we were working our way through the challenges on Preppin’ Data and one particular challenge (week 27), which looked like pretty straight forward at first sight, was giving me a headache from the start.
The aim of the challenge was to analyse “the effects of Valentine’s Day on the store sales”. The input file had 3 columns: store location, date and total sales for that date and the first step was to “determine if the sales date is pre- or post- Valentine’s day”. So far so simple.
My first instinct was to create a calculated field with an IF statement using the comparison operator > against the date of 14-02-2019, however the formula wasn’t working.
I read the error as “Tableau can’t compare data types that aren’t numeric” and set upon finding another solution. What the error message actually meant, is that Tableau can’t compare different data types in the same calculation. The solution to this was actually really easy: I simply needed to convert the date I was using as a reference point (Valentine’s Day) from a string to a date (do make sure to note the order of the date in string format!).
But before I discovered that, I went around it in a very DIY way, which you can find below:
1. Create 2 identical branches (one will be for pre Valentine’s dates and the other for post Valentine’s dates)
2. Filter the date column by Range of Dates to before 14/02/2019 for the “Pre” branch and to after 15/02/2019 for the “Post” branch
3. Create a calculated field with “Pre” and “Post” as the condition for their respective branch, which will create a populated column (do name those new columns with the same title)
4. Union the 2 branches back
