Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Aggregate Totals Using a Calculation

  • 14 February 2019
  • 3 replies
  • 223 views
J

Hi there,


I noticed there was a post awhile ago which asked how to aggregate the categories for fruit. I tried to follow this in my calculation, however the calculation doesn’t want to work because I am giving the expression 4 arguments over 3.


My data looks like the following:


NPS ORDINAL|NPS NESTED CALC|COUNT

|11|Promoter|4671|

|10|Promoter|1208

|9|Passive|742

|8|Passive|420

|1|Detractor|968

|6|Detractor|333

|7|Detractor|212

|2|Detractor|157

|4|Detractor|153

|3|Detractor|142

|5|Detractor|123


I want to calc the total number of promoters, detractors and passives and put this on a stacked bar chart to show distribution.


I am not sure if this is possible via calculations but I do not have access to lookml and I am fairly new to Looker.


Any help would be much appreciated!

3 replies

powellandy
Userlevel 3

Hi,


I assume the table you have shown us is the output from a query on the explore page?


I think if you click ‘pivot’ on the ‘NPS Ordinal’ field that will allow you to build the chart you want. You can then select bar chart, edit, stacked.


Andy

J

Hi Andrew,


Thanks for the response!


So I want my data to show the % stacked distribution for Promoters (5879/9129=64.4%), Detractors (2088/9129=22.9%) and Passives(1162/9129=12.7%). If I pivot on NPS ORDINAL (dimension), it just buckets by the numerical spread 0 - 10 and ignores the NPS NESTED CALC (calculation).

powellandy
Userlevel 3

Apologies, misunderstood the requirement.


I think this is a case where you should ask your developers to include it in the model instead. There’s something you could do with Custom Dimensions if you have that enabled, but I think that would be inappropriate in this case.


They should be able to create a new dimension which applies the same logic you are using in your table calculation. This is fairly standard logic that I’m sure will be beneficial to many other people. Table calculations and Custom dimensions are great but they only exist in the explore that created them, adding them to the model makes them re-usable in many different contexts.


Andy

Reply


Terms of Service