Is there a way to exclude null values for pivoted table calculation column?

  • 24 November 2021
  • 1 reply

For example i have data for 2019 and 2020 i wanted to calculate variance i get null for 2019 Variance due to data missing or not available to compare. I wanted to hide or remove this null column when I do that it hides variance for 2020 as well from visualization.

1 reply

Let me start by saying I’m not a Looker expert.  However, I think your issue is that you are “Pivoting” the data.  A Pivot on Year will present each measure for each year and the 2019 variance will be null because there is no 2018 data.  What you may want to do is create a measure for 2019 and one for 2020 then calculate variance using these measures.  For additional flexibility you may want to filter the Measures dynamically and name them Prior year, Current Year, and Variance.