what are you trying to do - it looks like you are trying to assign a "Holiday" Name ( ore weekend) to a date
if that is not the question the please explain
Tableau and excel don'e have the same architecture - its more like access - and it would be easier to accomplish your goal here by joining 2 tables and doing a conditional statement
Thanks for replying. I am trying to create a calculated field that indicates if it is a holiday or weekend. The excel file is attached if that is helpful.
I am not familiar with vlookup statements, so its hard to figure out how to create the calculated field.
Any help would be great!
This link is not specific to your particular worksheet,
but may be a useful general reference on weekends and holidays:
So the vlookup is looking to check whether or not the transaction date is a holiday, if it isn't check to see if it's Saturday/Sunday. I've replicated the look up in Tableau. I left joined your Data sheet with Holidays on Transaction Date and Gildead Holiday. You have to make sure both data types have been converted to dates before you make the join:
Then it's a just a calculated field to mimic the lookup:
[Transaction Date]= [Gilead Holidays]
DATEPART('weekday',[Transaction Date])= 7
DATEPART('weekday',[Transaction Date])= 1
Attached workbook version 10.5. Let me know if you have any questions!
273638gv.twbx 2.7 MB
Thanks for sending that over -
In a database oriented system like Tableau (or Access) Vlookup - or hlookup like you are doing here can be accomplished by joining 2 files together
I took your Holiday list and joined it to the Superstore data filter it for year and produced the attached
the process is shown below - on the data source tab - the formula date(Gilead Holiday) is there to convert what came in as a string to a date
what this day was place "Holiday" in a dimension called Holiday Indicaor)
then on the viz I added this calculation
what it does if the holiday indicator is NOT null it will take the value (Holiday") if it is null it will determine the day of the week and place it in a dimension that will be used as a filter
then create the viz - the first is detail
Just a list of dates and the sales value on that date (yours would be expenses)
Note I filter it so you are just seeing holiday sat and sun
the summary without dates would look like this
or the weekday summary would look like this
Note also If HR issues the new list of Holidays for next year just add them to the Holiday list and refresh the file in Tableau
holiday book.twbx 2.3 MB
This was SO helpful! And worked great.
I have another questions. Would you know how to write a tableau calculated field for this?
When cct.mis_industry_code >=3000 and cct.mis_industry_code <=3299 then 'Yes - Airline'
When cct.mis_industry_code >=3501 and cct.mis_industry_code <=3799 then 'Yes - Hotels'
When cct.mis_industry_code = 5411 then 'Yes - Grocery Store'
When cct.mis_industry_code = 5499 then 'Yes - Misc Food Store'
When cct.mis_industry_code = 5812 then 'Yes - Eating Places/Restaurants'
When cct.mis_industry_code = 5814 then 'Yes - Fast Food'
When cct.mis_industry_code = 7832 then 'Yes - Motion Picture Theaters'
This is really a separate question
Please mark my reply correct to close the first thread and post his as a new one
Also What is it you are trying to do - if there is a Text name for each Code you can join another table like above
Like Jim stated, this should be candidate for it's own question. Anyhow, I can't verify it, but something like this:
and cct.mis_industry_code <=3299
'Yes - Airline'
and cct.mis_industry_code <=3799
'Yes - Hotels'
cct.mis_industry_code = 5411
'Yes - Grocery Store'
cct.mis_industry_code = 5499
'Yes - Misc Food Store'
cct.mis_industry_code = 5812
'Yes - Eating Places/Restaurants'
cct.mis_industry_code = 5814
'Yes - Fast Food'
cct.mis_industry_code = 7832
'Yes - Motion Picture Theaters'
It seems you're fairly new to Tableau so here are some helpful links:
Thank you Gerardo!