Question

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

  • 17 April 2018
  • 5 replies
  • 93 views

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

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

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?

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

Hi Bernard,


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


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