Help with Average of Averages in Reference line

[tschullo]

I have a bar chart that shows the average hours spent on audits by team name in the "Reporting" phase of the project.
I'm only interested in hours logged in United States audits where the field RscActuals='Y'.

Since I am only interested in audits >= 2019, my Dimension is:  

=if([Audit Year]>=2019,[Lead Team])

My expression includes set analysis and looks like this:

Sum({<[Audit Task]={"Reporting"}, RscActuals={'Y'}, [Audit Region]={'United States'}>} Hours) /Count({<RscActuals={'Y'}, [Audit Region]={'United States'}>} DISTINCT [Audit ID])

In QlikView I had an "Average" reference line (built in) that I am trying to replicate in QS.

I have searched through the posts to find some help, but nothing is working for me.

It should be something like:

Avg(Total Aggr(
Sum({<[Audit Task]={"Reporting"}, RscActuals={'Y'}, [Audit Region]={'United States'}>} Hours)/Count({<RscActuals={'Y'}, [Audit Region]={'United States'}>} DISTINCT [Audit ID])
, [Lead Team]))

But I can't get it to work.  Please help!

[Lauri · Accepted solution]

You probably want to put the time filter into the set analysis instead of the Dimension:

Sum({<[Audit Year]={">=2019"}, [Audit Task]={"Reporting"}, RscActuals={'Y'}, [Audit Region]={'United States'}>} Hours) /Count({<[Audit Year]={">=2019"}, RscActuals={'Y'}, [Audit Region]={'United States'}>} DISTINCT [Audit ID])

Then your reference line can be:

Sum({<[Audit Year]={">=2019"}, [Audit Task]={"Reporting"}, RscActuals={'Y'}, [Audit Region]={'United States'}>} TOTAL Hours) /Count({<[Audit Year]={">=2019"}, RscActuals={'Y'}, [Audit Region]={'United States'}>} TOTAL DISTINCT [Audit ID])

[tschullo · Accepted solution]

Perfect! Thank you so much!

LetMeCount({<iReallyMeanIt={'True'}>} TOTAL Ways) 

😎

[tschullo]

Actually,  I spoke too soon, sorry.  The QlikView Average reference line is the average of the averages.

The total hours over the total audits is not the same number because it doesn't take into account the team averages.

[Lauri]

Ah, in that case I would try your Aggr, in your first post. I would expect it to work. Just add the Audit Year to its set analysis.

[tschullo · Accepted solution]

OK, now I have it. The premise of your answer was the correct response nonetheless.

Once I took the filter out of the dimension, my original approach which was failing at first now works:

=Avg(Total Aggr(
Sum({<[Audit Year]={">=2019"}, [Audit Task]={"Planning"}, RscActuals={'Y'}, [Audit Region]={'United States'}>} Hours)/
Count({<[Audit Year]={">=2019"}, RscActuals={'Y'}, [Audit Region]={'United States'}>} DISTINCT [Audit ID])
, [Lead Team]))

[tschullo]

I wonder if you could help me again.  We got this working a while back but now I have a new issue with the same code.

As you might recall I needed to know the average of averages displayed as bars. So if I had a bar1= 50, a bar2= 100, and a bar3= 150, the average would be 100 because the sum of the 3 is 300 divided by 3 gives me an average of 100.

The issue occurs when one of the bars has a zero value . In QlikView that bar can be suppressed (as in Qlik Sense as well) but the QV average reference line automatically takes it out of the equation. So if for example bar3= 0 and therefore suppressed, QV would sum the 2 non zero bars as 150 and divide by 2 to get an average of  75.  In QS, it is still dividing the sum of all 3, 150 by 3 and getting 50.

How do I get QS reference line to behave like QV?

My current expression is:

=Avg(Total Aggr(
Sum({<[Audit Year]={">=2019"}, [Audit Task]={"Reporting"}, RscActuals={'Y'}, [Audit Region]={'United States'}>} Hours)/
Count({<[Audit Year]={">=2019"}, RscActuals={'Y'}, [Audit Region]={'United States'}>} DISTINCT [Audit ID])
, [Lead Team]))

In English, this translates to give me the average of average Hours spent on audits by Lead Team where the year >=2019, the audit task is "Reporting" and the hours are flagged as RscActuals=Y in the United States region.

The added caveat that is missing is, don't count any Lead Teams where no time was spent on audits.

[Lauri]

Adding set analysis to include only Hours greater than zero (to both the measure and reference line) may address both issues:

Sum({<[Audit Year]={">=2019"}, [Audit Task]={"Reporting"}, RscActuals={'Y'}, [Audit Region]={'United States'}, Hours={">0"}>} Hours)/
Count({<[Audit Year]={">=2019"}, RscActuals={'Y'}, [Audit Region]={'United States'}, Hours={">0"}>} DISTINCT [Audit ID])

[tschullo]

Hmmm... nope, that didn't work either

[Lauri]

What does your data model look like?