Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Adding day of week to the table row but not in total row

  • 17 April 2018
  • 5 replies
  • 113 views
Makoto_Taguchi

Hi,


I have been trying to add day_of_week(Mon, Tue etc) to the simple table, and need help regarding how to not show it in total row. Here is what I did.


In Look ML I added this measure:


  measure: day_of_week_short {

    label: "day of week"

    type: string

    sql:

      CASE

        WHEN ${day_of_week_index} = 0 THEN 'Mon'

        WHEN ${day_of_week_index} = 1 THEN 'Tue'

        WHEN ${day_of_week_index} = 2 THEN 'Wed'

        WHEN ${day_of_week_index} = 3 THEN 'Thu'

        WHEN ${day_of_week_index} = 4 THEN 'Fri'

        WHEN ${day_of_week_index} = 5 THEN 'Sat'

        WHEN ${day_of_week_index} = 6 THEN 'Sun'

        ELSE ''

      END;;

  }

In the explorer, here is what I see. “Fri” shows up in total row which doesn’t make sense. I know there is another way to add day_of_week but I also need “filling in missing values” feature, so I ended up creating day_of_week_short as measure.



Is there any way not to show day_of_week_short in total row?


Thanks

5 replies

bernard.kavanagh
Rank
Userlevel 2

Hi Makoto,


The short answer here is to turn your measure into a dimension as totals are only available for measures. This will immediately remove “Fri” from the column total.


You can find more information on displaying totals here.


If you have any further questions please feel free to hop on chat or visit help.looker.com


Bernard

Makoto_Taguchi

Hi Bernard


Thank you. Yes I was considering the option but I also need “filling in missing values” feature as well, and I can’t use it if I have two dimensions(date and day of the week). Is there any workaround?

bernard.kavanagh
Rank
Userlevel 2

Hi Makoto,


Dimension Fill is available for dimensions of type yesno, tier, and most date types. It will also be applied to any dimension with a sql_case parameter.


Dimension Fill will turn on automatically for queries that run with a single dimension and/or a single pivot.


If you are not seeing this option in the options menu from the gear cog, could you provide a screenshot and some more details so we could investigate this further.


Many thanks,

Bernard

Makoto_Taguchi

Hi Bernard,


I was referring to this message: “Missing values can only be added for queries with one dimension.”


bernard.kavanagh
Rank
Userlevel 2

Right, I can see now. There are two options here, the first is if your day_of_week_short reverts to a measure, you can simply turn off totals.


The second option is to use a CONCAT() function for user_metrics_date and day_of_week_short which will look something like


dimension: day_of_week_short {

    label: "day of week"

    type: string

    sql:

      CASE

        WHEN ${created_day_of_week_index} = 0 THEN 'Mon'

        WHEN ${created_day_of_week_index} = 1 THEN 'Tue'

        WHEN ${created_day_of_week_index} = 2 THEN 'Wed'

        WHEN ${created_day_of_week_index} = 3 THEN 'Thu'

        WHEN ${created_day_of_week_index} = 4 THEN 'Fri'

        WHEN ${created_day_of_week_index} = 5 THEN 'Sat'

        WHEN ${created_day_of_week_index} = 6 THEN 'Sun'

        ELSE ''

      END;;

  }

  

  dimension: date_day_concat {

    type: string

    sql: CONCAT(${created_date}, " ",${day_of_week_short}) ;;

  }

With that, you can select date_day_concat as one dimension, thus working around the dimension fill problem.

You can also then apply a substring() table calculation to extract the day_of_week_short value:


substring(${orders.date_day_concat}, 11, 4)

This results in something like this:


Please let us know what you think of this workaround.


Bernard

Reply


Terms of Service