I have a table with a date dimension (month/year), grouping dimension and a metric value.
To compare with the previous year, I created a blend suggested in this post: how to show value of previous year metric in google data studio
When I group by the Executive dimension, the metric value for the previous year is lower than expected. This happens because executives who existed in the previous period but do not exist in the current period are not being counted.
Example: JAN 2026 vs JAN 2025
In the comparison image, it is possible to see that there are no NULL values for the current period (column VL_RECEITA) in the first table, and some values from January 2025 are missing. This indicates that executives who exist only in the previous period are being excluded from the comparison.
I am using a FULL OUTER, so I expected that all Executives from Jan/2025 should appear in the comparison.
Why are these rows still being excluded?
As per your EXECUTIVE dimension, what is it? EXECUTIVE (HISTORICO_ESTATICO)? Have you tried COALESCE(EXECUTIVE (HISTORICO_ESTATICO), EXECUTIVE(HISTORICO_ESTATICO_LAST_YEAR))? And same goes for the date, you’d need to create a new dimension in your LY, which would be this year (just like you created a last year for the join sake, but having it on the right table), and then on the table, it shouldn’t be Date but COALESCe(Date (HE), TY(HELY)), does it make sense?
1 Like
That was a great idea, and now the executive data from the previous period appears correctly. However, I can’t filter it by month because this would require using the same time dimension in the chart and the filter, in this case COALESCE(DATE(HISTORICO_ESTATICO), THIS_YEAR), within a blended calculated field. Since it’s not possible to create a calculated field that references columns from different tables, I’m unable to align the filter with the chart’s time dimension. Do you have any suggestions? Thank you in advance.
You can create a formula in a data visualization with metrics from different tables, you just can’t create it at the data source level. Not ideal, but for a limited use case of a few data visualization (ie the control, a table and a couple of graphs for instance), you definitively can do it. In your dataviz, click on Add a dimension, and then, add a calculated field, and you should be good to go
1 Like
I was going to say it sounds like the data range is filtered, but I was assuming you were using the compare to last year option.
As you are joining and dont have full people continuously, you need a complete list in your source, left joined to one year and left joined to the other year.
The first is a view or snapshot of the total people (no filters).
This is a driver list. The Left joins will only show if there is data and you can add a clause to convert nulls.
1 Like
As I mentioned in my previous comment, I was able to create the formula directly in the visualization, but to filter it by date I need the same field to exist both in the chart and in the filter control. Therefore, the calculation must be created at the blended data source level rather than only within the visualization, because if I recreate the same formula separately in the chart and in the filter, looker studio does not recognize them as the same field and the filter will not work. Thank you in advance.
The approach almost solved the issue. However, mapping both fields to share the same Field ID effectively forces one field to act as the other. In my case, both the table and the filter are based on the same blended data source, which brings me back to the original limitation: looker does not allow the creation of a calculated field at the data source level within a blend that references columns from more than one source. Thank you again for your help!
Would you mind sharing your report in Edit mode so I can have a look? I’m sure we can make it work, but playing with the actual data source would help me a lot here! You’ve got my contact on LinkedIn, sharing my e-mail through there!
