Filling in Dates, but not for dates but for integer numbers series in x axis.

  • 22 November 2021
  • 0 replies
  • 17 views

Some day someone asked how do I go from here:

Missing cases in an integer numbers variable in X-axis are not plotted. Only existing x cases have correspondent y values, but all are placed one after another.

To something like this:

The independent variable is regular spaced and missing values are plotted as having y=0.

I know, the very fast answer: go talk to your BD admin and ask him to build such x-axis independent variable.

But what if I feel brave enough to try it directly working on Looker?

Well, here are the steps I took to achieve it:

1.- First, grabbing any enough long in rows DB field, like ID for example, through ROW_NUMBER() function, I could set up a derived table that contains a regular paced variable with a proper alias.

derived_table: {

 sql: select row_number() over(order by Id) as consecutivos from                public.products;;

    }
 

2.- If the field with jumps is a Dimension, convert it to a Measure. Using this awesome trick 

3.- Merge accordingly, taking as primary query the regular paced x-axis independent variable that was made before. And match it with the dimension with jumps. So it will generate Null values where it doesn’t find a match. And include the correspondent measure. At the end, I got something like this:

 

Anyway. This is as far as I got. I hope you may find it useful.

 

Blessings.

Juan P


0 replies

Be the first to reply!

Reply