Question

How can I aggregate values based on a calculation?

  • 22 February 2017
  • 3 replies
  • 1961 views

I want to create a visualization based on a calculation but each row is showing up in my visualization.


Field “Item Name” | Calculation “Category” | Count

Apple | Fruit | 2

Orange | Fruit | 4

Onion | Vegetable | 1

Banana | Fruit | 2


I would like to hide the Field “Item Name” and get


Calculation “Category” | Count

Fruit | 8

Vegetable | 1


Instead I am getting


Calculation “Category” | Count

Fruit | 2

Fruit | 4

Vegetable | 1

Fruit | 2


So far I have a Label calculation that sets up my category:

if( row() = 1 , “Fruit” , if( row() = 2, “Vegetable”, “” )


but I can’t figure out how to sum the count category based on a condition. If I could do that I would hide everything but the first 2 rows.


3 replies

Userlevel 3

@tcarrw


For the specific example above, this table calc should work:


if(row()=1, sum(if(${category} = "Vegetable",${count},0)),sum(if(${category} = "Fruit",${count},0)))

Then limiting the viz to the first 2 rows would give you this:



However, this only works under the assumption that the first two rows will have one of each kind of thing you want. Changing the sort would mess it up.


An alternative way to do this would be to create a Category dimension in the view using a case statement.


dimension: category {
type: string
sql: case when ${item_name} in ("Apple","Orange","Banana") then "Fruit"
else: "Vegetable"
end ;;
}

This would allow you to select category and the grouping would be done for you:


Thanks a lot for your help. I was trying to get creative without LookML access but some of my looks have 20+ categories. Is there a way to do this if I keep my Label Calculation and Count Calculation separate?


The statement below is only true when the Label Calc and Label are equal.

if( {LabelCalculation} = {Label}, …


I would love my {count} Calculation to be able to change “Vegetable” to the value in row 2 of the {LabelCalculation}. Can I do something with the value of the index position or offset?

Userlevel 3

It’s not currently possible to reference a single row of the table calc in that way. The example above works well for two categories but if you’re data set is more complex than that it would be more useful to generate the categories within LookML. This would give a lot more flexibility on the front end.

Reply