Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
caiogil1
Contributor III
Contributor III

Average, Aggregate and Sum - Calculating the Average by Dimension

Hi all,

I am having this problem calculating the Average of VALUE to each STATE.

The formula that I used is avg(aggr(sum(VALUE)/count(HOSPITAL),STATE)) as shown in the image.

So as you can see this work Ok for some rows (First occurrence of HOSPITAL) but for other rows I am getting Null (even that I just let "count(HOSPITAL)" instead of "sum(VALUE)/count(HOSPITAL)" ) I will get Null.

The goal is that each row has the average calculated correctly like others rows (values in the bottom table for each State).

 

Capturar.PNG

Someone know how can I solve this problem? Smiley Happy

 

I am attaching the QVF that I used, is just an example with an inline.

load * inline [
HOSPITAL,VALUE,STATE
A,100,PA
B,20,CE
C,30,PA
D,80,DF
E,20,CE
F,30,RJ
G,50,RJ
H,50,PA
];

I hope to learn a lot here and help if I can
Labels (3)
1 Solution

Accepted Solutions
mcsshg2011
Contributor III
Contributor III

Hi, 

I think you need the word NODISTINCT.

Aggr(NODISTINCT SUM(VALUE)/Count(HOSPITAL),STATE)

Qlik Aggregate.png

View solution in original post

1 Reply
mcsshg2011
Contributor III
Contributor III

Hi, 

I think you need the word NODISTINCT.

Aggr(NODISTINCT SUM(VALUE)/Count(HOSPITAL),STATE)

Qlik Aggregate.png