Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
JustInTime
Contributor
Contributor

Total average by field, without chart dimension

Hi everyone, 

I've got an annoying problem that I've managed to solve in some cases but doesn't fit specifically what I'm trying to do. 

I have data that looks like the following:

IDPeriodNumerator
12018044
12018045
12018047
12018054
220180614
220180615
220180717
220180714

 

And I want to transform it into the following:

PeriodAverage of Numerator
2018044.666...
2018054.666...
20180615
20180715

 

Where Average of Numerator is the average of all Periods for each ID. 

I have accomplished this with the following expression:
avg(total <ID> aggr(avg(Numerator), Period))

But in order for this to work I need to have ID as a dimension in the chart. I don't want to have ID as a dimension because I will be using this expression in a line graph alongside other expressions. If I use a line graph with other expressions, the change in average according to the ID doesn't happen. 

I have attached a test file and test data that hopefully shows what I mean. 

Labels (2)
1 Solution

Accepted Solutions
Taoufiq_Zarra

@JustInTime  why is 4.666 not 5

If I understood correctly you need

dimension : Periond

measure :

=aggr(nodistinct avg( Numerator),ID)

 

output:

Capture.PNG

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

2 Replies
Taoufiq_Zarra

@JustInTime  why is 4.666 not 5

If I understood correctly you need

dimension : Periond

measure :

=aggr(nodistinct avg( Numerator),ID)

 

output:

Capture.PNG

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
JustInTime
Contributor
Contributor
Author

Amazing, thank you. This worked, using nodistinct was the key.