Solved

# Pivot Table Calculation

• 6 replies
• 854 views

• Member
• 8 replies

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?

icon

Best answer by Naomi_Johnson 18 March 2021, 22:09

View original

This topic has been closed for comments

### 6 replies

Hi iglooburner,

It is not possible to pivot table calculations in the way you would like as they create a new column under every pivoted dimension.

The best way to do this would be to add another timeframe to your `cases_per_hour_kpi.work_dt` dimension group, which is `day_of_week`. This will give you Monday to Sunday, which you can then pivot. This ensures that the code is version controlled, can be used in other tiles and Looks and the calculation is simpler so your query will be quicker than my second solution.

``dimenison_group: work_dt {  type: time  timeframes: [raw, date, day_of_week, ...]  ...}``

If you don’t have access to the LookML and it’s not possible to ask a LookML developer to add this dimension group, you may have permission to create a custom dimension. You can pivot this.

`list` and `index` functions cannot be used in custom dimensions, so the function is a lot longer.

``if(  mod(diff_days(date(2008,01,01), \${order_items.created_date}) + 1, 7) = 0  , "Monday"  , if(    mod(diff_days(date(2008,01,01), \${order_items.created_date}) + 1, 7) = 1    , "Tuesday"    , ... continued for all days up to Sunday  ))``

Hope this helps.

Naomi

Thank Naomi - I tried the second part (adding a dimension) but I get expression incomplete when I use the following:

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"
,if(
mod(diff_days(date(2008,01,01), \${cases_per_hour_kpi.work_dt}) + 1, 7) = 6
, "Sunday"
)
)

What am I missing/doing wrong?

Userlevel 7 +1

``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")  )``

This should work. after you get your IFs for 0-5, then the only other option is 6, hence it works as else

Thanks Dawid I tried your note above exactly as follows:

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") )

However, I still get expression incomplete (and unable to run query error) - thoughts?

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

Thanks Naomi - that works perfectly!  Very much appreciated!