Calculate Standard Deviation and Percentiles by Country

How can I get a table where I can see the country code, the count of accounts created and the standard deviation, 75th percentile, 25th percentile by country? 

I have been trying to do it but I can´t for the life of me get how to do it. I have tried creating measures on LookML but they didn’t work.

0 2 1,130
2 REPLIES 2

jcdufault
Participant II

Hi Andrea_gr, 

Create a Look with your country code and the count of accounts created. Then add a table calculation and refer to the Looker Functions and Operators documentation page

You could create a table calculation like so to find your standard deviation:

stddev_pop(${accounts.count_of_accounts})

There is also a percentile function.

percentile(${accounts.count_of_accounts},0.25)

Hope this helps

Hi Team , 

How to replicate below sql in bigquery .

 

select distinct [Peer Group] ,PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY [Spend per Revenue]) OVER (PARTITION BY [Peer Group] ) AS percentile_80
FROM GROUP_DATA
where [Spend per Revenue] <= 0.0002 and [Spend per Revenue] >0
Top Labels in this Space
Top Solution Authors