Aggregate Totals Using a Calculation

done
low_priority
reply

(Janet May) #1

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!


(Andrew Powell) #2

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


(Janet May) #3

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).


(Andrew Powell) #4

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