Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Solved

Pivot Table Calculation

  • 15 March 2021
  • 6 replies
  • 854 views
iglooburner

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

Naomi_Johnson
Userlevel 2

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

Naomi | Redkite
iglooburner

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?

Dawid
Userlevel 7
Badge +1

You’ve added too many IFs

 

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

Dawid
iglooburner

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?

Naomi_Johnson
Userlevel 2

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

Naomi | Redkite
iglooburner

Thanks Naomi - that works perfectly!  Very much appreciated!

Terms of Service