Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
mamaewapolya
Contributor II
Contributor II

Calculating same average value for a KPI as in the table

Hi everyone,

I am trying to make aligned the very last column of the table, which is RangeAvg of the MS1-MS6 percentages.

mamaewapolya_1-1674487422055.png

I am using the following formula for the KPI (trying to find an average for the RangeAverage and aggregate for all orders):

Avg(Aggr(RangeAvg([MS1 Quality],[MS2 Quality],[MS3 Quality],[MS4 Quality],[MS5 Quality],[MS6 Quality]),OrderNo))

Please advise what might be wrong in my calculation or logic.

Thank you!

Polina

Labels (1)
1 Solution

Accepted Solutions
mamaewapolya
Contributor II
Contributor II
Author

Just found the solution - you have to aggregate by all dimensions you have in the table. Except for OrderNo I also had a couple of columns with dates. So, basically, it should look like this:

Avg(Aggr(RangeAvg([Measure 1],[Measure 2],...[Measure n]),Dimension 1, Dimension 2,...Dimension n))

View solution in original post

6 Replies
KGalloway
Creator II
Creator II

If it is always MS1-MS6, could you just do the following for the KPI?

(avg([MS1 Quality]) + ... + avg([MS1 Quality])) / 6

 

If not, could you provide some more details about the data structure? What is OrderNo used for? An small sample table of data would be useful as well if you can provide one.

mamaewapolya
Contributor II
Contributor II
Author

Hi,

No, this is not working - (avg([MS1 Quality]) + ... + avg([MS1 Quality])) / 6.

I tried to test a formula for average for MS1 first, but it gave me 96% instead of expected 98%. I used "Total" after Set Expression.

Please find attached the sample data.

Polina

KGalloway
Creator II
Creator II

I may be misunderstanding the data structure. I assumed it looked like the table on the left in my screenshot below. With that structure, I was able to get the numbers to match using 

avg([MS1 Quality] + [MS2 Quality] + [MS3 Quality] + [MS4 Quality] + [MS5 Quality] + [MS6 Quality]) / 6

 

KGalloway_0-1674570318035.png

 

vinieme12
Champion III
Champion III

Works fine for me

AVG(aggr(RangeAvg([MS1 Quality],[MS2 Quality],[MS3 Quality],[MS4 Quality],[MS5 Quality],[MS6 Quality]),OrderNo))

 

vinieme12_2-1674628091460.png

 

 

vinieme12_1-1674628055379.png

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

can you post the app itself?

 

check if the KPI is tagged to any alternate state

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
mamaewapolya
Contributor II
Contributor II
Author

Just found the solution - you have to aggregate by all dimensions you have in the table. Except for OrderNo I also had a couple of columns with dates. So, basically, it should look like this:

Avg(Aggr(RangeAvg([Measure 1],[Measure 2],...[Measure n]),Dimension 1, Dimension 2,...Dimension n))