I am trying to convert a date into a week day name, and have it displayed underneath the full date in the visualization.
For example, my data shows dates across the top (3/1/2021, 3/2/2021, etc). Underneath the dates I would like to display the day of the week.
I did a table calculation to convert the date into a day name using the following:
coalesce(
index(
list("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"),
mod(diff_days(date(2021,03,01), ${cases_per_hour_kpi.work_dt}) + 1, 7)
) , "Sunday")
However, when I try to Pivot the Table calculation, it shows up in each row and column. I just want to show Monday underneath the date - currently it shows Monday next to each row of date in the entire column.
Any ideas?
Solved! Go to Solution.
Hi iglooburner,
There are 6 if functions, so there need to be 6 brackets at the end of the expression:
if( mod(diff_days(date(2008,01,01), ${cases_per_hour_kpi.work_dt}) + 1, 7) = 0 , "Monday"
,if( mod(diff_days(date(2008,01,01), ${cases_per_hour_kpi.work_dt}) + 1, 7) = 1 , "Tuesday"
,if( mod(diff_days(date(2008,01,01), ${cases_per_hour_kpi.work_dt}) + 1, 7) = 2 , "Wednesday"
,if( mod(diff_days(date(2008,01,01), ${cases_per_hour_kpi.work_dt}) + 1, 7) = 3 , "Thursday"
,if( mod(diff_days(date(2008,01,01), ${cases_per_hour_kpi.work_dt}) + 1, 7) = 4 , "Friday"
,if( mod(diff_days(date(2008,01,01), ${cases_per_hour_kpi.work_dt}) + 1, 7) = 5 , "Saturday" ,"Sunday"))))))
Best,
Naomi