Basically, if my x-axis is time and y-axis is ‘download speed’ I want to have a line chart where each line is dimension ‘a’. I’d like this chart to answer the question “for dimension A, what is the speed at this time?”
I know this can be achieved by pivoting dimension A (thus, making each value of dimension A a line in my chart). However, I am already pivoting by dimension B since I also want my chart to be split up (small multiples) based on dimension B. This means I’m doing Plot → Grid Layout → By Pivot already in the visualization.
Having two pivots means too many charts are created! I want to do grid layout by pivot for dimension B only. I want the series to be dimension A only. Is this possible? I tried using ‘edit chart config’ but honestly I’m not getting much results. Any help is appreciated!
You can only create a series by adding measures or pivoting existing measures on a dimension. Conversely, if you want to restrict the outputs of a pivot, you could filter down to the dimensions you are interested in.
The only options I can think of are filtered measures specific to your needs, or perhaps a merged query? A merged query would allow you to introduce another series from the same explore, but without the same filtering or (perhaps) pivoting.
Is there a way to dynamically filter the measure? Right now the dimension I want to ‘group’ or pivot by are these bins I am making dynamically:
dimension: download_bin_test {
type: string
sql: CASE
WHEN ${agg_val_download_kbps} >= {% parameter aggergate_from %}
THEN CONCAT(“>”, CAST({% parameter aggergate_from %} AS STRING))
ELSE CONCAT(
CAST(FLOOR(${agg_val_download_kbps} / {% parameter bin_size %}) * {% parameter bin_size %} AS STRING),
" - ",
CAST((FLOOR(${agg_val_download_kbps} / {% parameter bin_size %}) + 1) * {% parameter bin_size %} - 1 AS STRING)
)
END ;;
description: “Dynamic human-readable bins for download speed, based on bin size.”
}
So my goal is to create a line chart that has a line per bin - where x axis is time and y axis is speed. So now my problem is- okay I can create filtered measure for finding the average per bin, but since bins are created dynamically I don’t know how many there would be it’s based on user input.
Perhaps a line chart is the wrong way to visualise this if you don’t know how many bins are going to be created? Would a table with conditional formatting make more sense?
The line chart is more for visualizing the change over time. Is there no way to use my bin dimension to group the measure? If there is no way around this, I understand, and maybe a table would be more ideal. Thank you
You can indeed pivot on a tiered dimension - this article about dynamic tiers has some links to other docs on tiered dimensions in Looker. You can also bin values from the explore by clicking on the three dot menu in the field picker beside the dimension you want to bin on, as below. You can pivot on these dimensions also.
