Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Fquiroga95
Contributor II
Contributor II

AVG and AGGR on a straight table chart.

Hi, what I would like to achieve is something like this:

 

Fquiroga95_0-1662049422441.png

 

I have weeks, distributors, countries and segments..

 

Revenue is a metric calculated by sum(units*price).

I would like an average of revenue per week and segment, in each row, like in the table I created.

So this should apply for every week (3,4,5,etc).

I tried with the next expression:

 

 

 

=AVG(


AGGR(
 Sum(Units*Price),

Week,Segment)
)

 

 

But, what I am getting is just one value, in one record, and the value is not the average is the total...

 

Fquiroga95_1-1662049898954.png

 

I don't know what I am doing wrong.

 

 

I would appreciate some help.

 

Regards.

 

 

 

2 Replies
sidhiq91
Specialist II
Specialist II

@Fquiroga95  Just a small change to your formula

Create a revenue field in the back end itself.

In the front end use the below formuls:

Avg( Aggr(nodistinct Avg(Revenue),Week,Segment))

This should resolve the issue.

Fquiroga95
Contributor II
Contributor II
Author

Let´s say is not possible to create a field in the back end, there is a very rigid methodology of work, tables can't be modified and every formula is a variable..

 

So  "Sum(Units*Price)" is actually a variable created in the backend as vRevenue.

 

So what I was doing is:

=AVG(

AGGR(
 $(vRevenue),

Week,Segment)
)

 

I tried with

=AVG(

AGGR(

AVG(
 $(vRevenue)

),

Week,Segment)
)

 

But it didn't work either