topic Re: Help with Average of Averages in Reference line in App Development

Help with Average of Averages in Reference line

tschullo — Sat, 16 Nov 2024 03:53:00 GMT

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!

Re: Help with Average of Averages in Reference line

Lauri — Wed, 11 Dec 2019 20:55:39 GMT

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

Re: Help with Average of Averages in Reference line

tschullo — Wed, 11 Dec 2019 21:10:45 GMT

Perfect! Thank you so much!

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

😎

Re: Help with Average of Averages in Reference line

tschullo — Wed, 11 Dec 2019 21:29:56 GMT

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.

Re: Help with Average of Averages in Reference line

Lauri — Wed, 11 Dec 2019 21:33:40 GMT

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.

Re: Help with Average of Averages in Reference line

tschullo — Wed, 11 Dec 2019 21:35:21 GMT

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

Re: Help with Average of Averages in Reference line

tschullo — Thu, 20 Feb 2020 21:50:27 GMT

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.

Re: Help with Average of Averages in Reference line

Lauri — Fri, 21 Feb 2020 02:22:54 GMT

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

Re: Help with Average of Averages in Reference line

tschullo — Fri, 21 Feb 2020 13:46:53 GMT

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

Re: Help with Average of Averages in Reference line

Lauri — Fri, 21 Feb 2020 21:54:01 GMT

What does your data model look like?

Re: Help with Average of Averages in Reference line

tschullo — Fri, 21 Feb 2020 22:32:01 GMT

Team Audit Hrs Rsc Ctr Rsc ID

A A123 5 France 1

A A103 15 Canada 2

B A123 15 France 3

B A123 25 Canada 4

C A111 30 Canada 5

C A123 50 Canada 6

If I have no ctry selection bars are A=10, B=20, C=40. Average should be 35. Current expression works.

But if I select France, bars should be A=5, B=15 and average would be 10, not 20/3.

I'm not at work today, will give you exact model Monday.