Hi,

I have an issue using nested functions.
I have a GA datasource where I get some metrics and dimension, including ga:date.

I want to display a dimension sorted by a metrics filtred on ga:date.
I use
- GROUPBY to calculate the sum of the metric based on the dimension
- SORT for sorting the result

and a SELECT to filter the data

GA datasource structure:

A: ga:date / B: fullReferrer / C: ga:users / D: ga:sessions / E: ga:bounces

The wanted result is the bounceRate by fullRefferer filtred on date between startDate and endDate, all sorted by numbers of users

Here is my query, but it's return empty result.

SELECT (
SORT (
GROUPBY ( SLICE(B:B) , SLICE(C:C) ) , descending numeric , GROUPBY ( SLICE ( B:B ) , SUM (  SLICE(E:E) /  SUM(SLICE( D:D ))  ) )
)

, BETWEEN( DATE(A:A,"yyyyMMdd") , startDate , endDate ) )

What I did wrong ?

• Keely Davison

For this formula you need to select the data based on the date range and then group it using GROUPBY.  Also the GROUPBY takes care of summing the data souse it to SUM bounces and sessions for the date range and do the division on the summed total.

The formula will look something like

SORT (
GROUPBY ( SELECT( SLICE(B:B), BETWEEN( DATE(A:A,"yyyyMMdd") , startDate , endDate ) ) ,

SELECT( SLICE(C:C) , BETWEEN( DATE(A:A,"yyyyMMdd") , startDate , endDate ) )) ,

descending numeric ,

(GROUPBY ( SELECT( SLICE(B:B), BETWEEN( DATE(A:A,"yyyyMMdd") , startDate , endDate ) ) ,

SELECT( SLICE(E:E) , BETWEEN( DATE(A:A,"yyyyMMdd") , startDate , endDate ) ) /

GROUPBY ( SELECT( SLICE(B:B), BETWEEN( DATE(A:A,"yyyyMMdd") , startDate , endDate ) ) ,

SELECT( SLICE(D:D) , BETWEEN( DATE(A:A,"yyyyMMdd") , startDate , endDate ) )

) )
)