Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
caiogil1
New 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
];

1 Solution

Accepted Solutions
mcsshg2011
New Contributor III

Re: Average, Aggregate and Sum - Calculating the Average by Dimension

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
New Contributor III

Re: Average, Aggregate and Sum - Calculating the Average by Dimension

Hi, 

I think you need the word NODISTINCT.

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

Qlik Aggregate.png

View solution in original post