Data Studio
2.2K members online now
2.2K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Returning the maximum value of a table column

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hello,

I was wondering if someone could help me return the highest value of "Total Score".

Total score is calculated metric.

 

I have tried the following:

MAX(Total Score)

I get an error: Re-aggregating metrics are not supported.

 

I would like to extract the number 10.2 as the maximum value so I can add another column which calculates it as a percentage i.e. 10.2 = 100%, 8.3 = 81.37%, 5.61 = 55%

 

Thanks in advance,

Jason

Returning the maximum value of a table column

Explorer ✭ ✭ ☆
# 2
Explorer ✭ ✭ ☆

hi @Jason C

 

How do you calculate your field "Total Score" ? is it a calculated field with a formula ?

Which aggregation have your field "Total Score" ? 

 

 

You can't use MAX because your formula contain the field "Total Source" which have an aggregation different of "None"

Returning the maximum value of a table column

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Patrick, what is an alternative solution to this issue ? I am stuck on the same..

Returning the maximum value of a table column

Visitor ✭ ✭ ✭
# 4
Visitor ✭ ✭ ✭

Hi Patrick,

 

Yes you are correct, It is a calculated field with a formula.

Is there a work around for this?

Re: Returning the maximum value of a table column

Explorer ✭ ✭ ☆
# 5
Explorer ✭ ✭ ☆

so, your field "Total Score" is a field with type "numeric" and aggregation "auto", i guess. 

I need to know how do you calculate your field "total score". Which formula is it?

 

Try this formula to have your MAX:

MAX(Total Score*1)

 If it's not working, i very need to see how do you calculate your field "Total Score"

Returning the maximum value of a table column

[ Edited ]
Visitor ✭ ✭ ✭
# 6
Visitor ✭ ✭ ✭

Hi Patrick,

 

That did not work. It is numeric with auto aggregation.

 

This is how Total Score is calculated:

 

(Sessions WEIGHTED * Weighting Value Big) + (Avg. Time on Page WEIGHTED * Weighting Value Small) + (Avg. Session Duration WEIGHTED * Weighting Value Small) + ((Pages / Session * 0.5) * Weighting Value Small)

 

The formulas that exist in here are:

 

Sessions Weighted is:

((Sessions / Date Count From Date Selector) / 50)

 

Date Count From Date Selector is:

DATE_DIFF(TODATE(Date, 'DEFAULT_DASH', '%Y-%m-%d'), TODATE(Date, 'DEFAULT_DASH', '%Y-%m-%d'))

 

Avg. Time on Page WEIGHTED is:

((Avg. Time on Page * 1440) / 100000)

 

Avg. Session Duration WEIGHTED is:

((Avg. Session Duration * 1440) / 100000)

 

Weighted Value Small:

1 * 0.05

 

Weighted Value Big:

1 * 0.85

 

It is a fairly complicated formula. Thanks for your help so far.

Re: Returning the maximum value of a table column

Explorer ✭ ✭ ☆
# 7
Explorer ✭ ✭ ☆

hi @Jason C

 

Problem is aggregation with your fields. You will have to be clever  and to play with aggregation Smiley Happy

For this 4 fields, take off the average in your formula and set in in the aggregation, then you will have : 

Avg. Time on Page WEIGHTED is, with aggregation "Average" :

((Time on Page * 1440) / 100000)

Avg. Session Duration WEIGHTED is, with aggregation "Average" :

((Session Duration * 1440) / 100000)

Avg. Time on Page WEIGHTED is, with aggregation "Average" :

((Time on Page * 1440) / 100000)

Avg. Session Duration WEIGHTED is, with aggregation "Average" :

((Session Duration * 1440) / 100000)

 

IMPORTANT : Before to create your "Max Total score", set the aggregation of those 4 fields to "None". 

You will see that your field "Total score" will have now an aggregation "Auto" or "None". If it's not "None", you can change it to "None"

 

Now you can create your field "Max Total Score" with the formula : 

MAX(Total score * 1) // You can even do (Total Score*1) and in the aggregation of the field, set "MAX"

IMPORTANT : Now that you have your "Max Total score", set the aggregation of those 4 fields to "Average" for having your good values. 

Re: Returning the maximum value of a table column

Visitor ✭ ✭ ✭
# 8
Visitor ✭ ✭ ✭

Hi Patrick,

Thanks for your suggestion. I must be missing something as I can not set the aggregation when I created a new field:

Picture 2017-05-11 at 9.56.21 AM.jpg

Thanks,

Jason