Question

Pie chart on a calculated column

  • 24 May 2017
  • 1 reply
  • 442 views

I have a calculated column PO Status with values Valid and Invalid.


I want to aggregate and visualize this column with a pie chart. How can i do this in looker


1 reply

Userlevel 3

@Ram,


In Looker, pie charts can display a maximum of 50 rows of data and require exactly one dimension and one measure. If there are less than 50 rows in your example data then we can create a complicated table calc that would achieve this for you.


First we create a calculation that sums the results based on the value.


Calculation 1:


if(${PO Status} = "Valid PO"
,sum(if(${PO Status} = "Valid PO",1,0))
,sum(if(${PO Status} = "Invalid PO",1,0)))

Then we create a calculation that returns the max of this in the first row and the min of this in the second row. (Since there are only two values, this works but it wouldn’t work if we had more than 2.)


Also, since we want this calculation to be treated as a measure we can include some other measure in the report and add this multiplied by 0 to the table calc to ensure Looker treats the table calc as a measure.


Calculation 2:


if(row()=1,max(${Calculation_1})
,if(row()=2,min(${calculation_1})
,null))+(${view_name.measure_name}*0)

If we do all of this inside one table calc then it looks like:


New Calculation:


if(row()=1,max(${if(${PO Status} = "Valid PO"
,sum(if(${PO Status} = "Valid PO",1,0))
,sum(if(${PO Status} = "Invalid PO",1,0)))})
,if(row()=2,min(${if(${PO Status} = "Valid PO"
,sum(if(${PO Status} = "Valid PO",1,0))
,sum(if(${PO Status} = "Invalid PO",1,0)))})
,null))+(${view_name.measure_name}*0)

Then for each field except for ‘PO Status’ and ‘New Calculation’ we can click on the gear and ‘Hide from Visualization’ so that the vis only looks at one dimension and one measure. This should result in a nice pie chart with 2 sections, although the legend may not display as expected.


An alternative way to achieve the same visualization would be to make use of a yesno dimension in lookml. We could create a dimension with the same logic as the table calc to determine if the po is valid or not. Something like this but inserting your logic instead of ‘is valid’:


  dimension: is_valid_po {
type: yesno
sql: ${purchase_order_number} is valid ;;
}

Then in our explore, we just need to return this dimension and a count measure to create the same pie chart.


Hope this helps. If you have further questions specific to your data, feel free to visit help.looker.com and we’ll be happy to take a look at it with you.

Reply