Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Fquiroga95
Contributor II
Contributor II

AVG, AGGR on straight table.

Hi, the next table is what I need to achieve...

 
 

Fquiroga95_2-1662118067291.png

 

So week, distributor, etc are dimensions.

Revenue is a metric created as a variable in the backend, basically is:

vRevenue=sum(SalesRevenue*$(vCurrency)), and it depends on the front end selection of Currency. 

 

I need to get the average of the revenue like in the table showed by week and segment.

 

For example, in the first case, week 2, lower midmarket segment: I have revenue of = 18718.42 + 99420.4 + 1878.11. As there are three lines, the average would be (18718.42 + 99420.4 + 1878.11 / 3 )= 120017.

I need that number in each line. Could be more than three lines, that's an example, but basically the average by week, and then by segment, depending of the amount of records in the table.

 

I don't know how to achieve the AVG columb, I tried with AGGR:

=AGGR(NODISTINCT
Num(avg(SalesRevenue*$(vCurrency))
,Week,Segment)

But it doesn't work, it brings me the wrong values.

 

Would appreciate any help.

 

 

1 Reply
rubenmarin

Hi, I think you need TOTAL qualifier here, try with:

Avg(TOTAL <Week,Segment> SalesRevenue*$(vCurrency))

TOTAL is used to ignore dimensions, and group values by the fields defined between <>