topic Re: Help with Average in Reference line in QlikView

Help with Average in Reference line

Fri, 16 Oct 2015 20:37:30 GMT

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

Re: Help with Average in Reference line

Fri, 16 Oct 2015 20:59:21 GMT

I been trying this, but it doesn't work??

avg(aggr(count(DISTINCT{$<soccer={"ball"}>}Incident_ID) / count (DISTINCT Incident_ID)))

Re: Help with Average in Reference line

swuehl — Fri, 16 Oct 2015 23:03:16 GMT

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

Re: Help with Average in Reference line

swuehl — Sat, 17 Oct 2015 12:24:02 GMT

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.

Re: Help with Average in Reference line

Mon, 19 Oct 2015 14:19:39 GMT

That works, thank you.

any chance you know how to make that into a percent in a text box?

Re: Help with Average in Reference line

swuehl — Mon, 19 Oct 2015 16:17:41 GMT

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%','.',',')

Re: Help with Average in Reference line

Mon, 19 Oct 2015 17:53:16 GMT

perfect, thank you so much