Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Guys,
I'm was wondering if someone could help me with an AVG in a reference line
I have a chart that uses this calculation to give me a percent for 12 months.. I want to show a reference line that shows the average from all the months.
=num (count (DISTINCT{$<soccer={"ball"}>}Incident_ID)
/
count (DISTINCT Incident_ID))
Hope I explained this right.
thanks,
Fred
If you want to calculate the share in the context of all possible records, you can use the same expression as for the dimensional split:
=num (count (DISTINCT{$<soccer={"ball"}>}Incident_ID) / count (DISTINCT Incident_ID))
But it seems you want the average of the monthly shares, something like
=Avg( Aggr( count (DISTINCT{$<soccer={"ball"}>}Incident_ID) / count (DISTINCT Incident_ID), YourMonthField))
I been trying this, but it doesn't work??
avg(aggr(count(DISTINCT{$<soccer={"ball"}>}Incident_ID) / count (DISTINCT Incident_ID)))
If you want to calculate the share in the context of all possible records, you can use the same expression as for the dimensional split:
=num (count (DISTINCT{$<soccer={"ball"}>}Incident_ID) / count (DISTINCT Incident_ID))
But it seems you want the average of the monthly shares, something like
=Avg( Aggr( count (DISTINCT{$<soccer={"ball"}>}Incident_ID) / count (DISTINCT Incident_ID), YourMonthField))
You are missing dimension(s) for your advanced aggregation, the aggr() function. Something like the YourMonthField I used in my example (which you need to replace with your field from your data model.
That works, thank you.
any chance you know how to make that into a percent in a text box?
Both formulas should return the value also in a text box. Have you tried this?
You can enclose the formulas in a num() function to format as percentage:
=Num( Avg( Aggr( count (DISTINCT{$<soccer={"ball"}>}Incident_ID) / count (DISTINCT Incident_ID), YourMonthField)), '#0.00%','.',',')
perfect, thank you so much