Solved: Pivot Table Calculation - Page 2

iglooburner · 03-15-2021 12:20 PM

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?

Naomi_Johnson

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

View solution in original post