# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

## Solving function

Hi everyone,

I do need a hand to solve this formula:

Sum(aggr(avg({\$<[Type]={0}>}Price*Sum (Qty)),ItemNumber, Type, ))

Thanks a lot

19 Replies
MVP

## Re: Solving function

What is the problem you are facing?

Honored Contributor II

## Re: Solving function

you can't use Sum inside Avg.

maybe this:

Sum(aggr(avg( { \$<[Type] = {0} >} Price*Qty ), ItemNumber, Type ) )

Not applicable

## Re: Solving function

Hi Andrea,

I've already used Sum within the AVG function in the past and it worked, I think there is sth wrong with restriction part -> { \$<[Type] = {0} >}

best,

Flo

Not applicable

## Re: Solving function

Hi Sunny,

I do get "0" as answer which is not the correct answer coz I want to get the turnover for all my item but only them with type=0

Best,

Florian

Honored Contributor II

## Re: Solving function

i think you should use the set analysis in all aggregation functions you have in your expression.

Valued Contributor

## Re: Solving function

Tis might be silly but try

Sum(aggr(avg({\$<[Type]={'0'}>}Price*Sum (Qty)),ItemNumber, Type, ))

Regards,

Pankaj

Valued Contributor

## Re: Solving function

Hi Florian,

You cannot use Sum inside Average as suggested by Andrea.

Aggregation won't make any sense when we have sum inside Avg.

Regards,

Pankaj

Valued Contributor III

## Re: Solving function

I think that won't make a difference in the result.

Not applicable

## Re: Solving function

You are right for the Sum() within the AVG(), I do get a result if I erease the AVG() but however I don't get the correct answer.

The problem I'm facing is that in my firm we do have price differences during the year depending on the orders so I use the AVG() to get average price which I multiply with my quantity.

My basic function which return the correct answer (I checked it by exporting the data from my qlik sense table and exporting them to Excel) is the following one:

Sum(aggr(avg(Price)*sum(Quantity),ItemNumber, Type))

That's why I tried to write it like:

Sum(aggr(avg({1<[Type]={0}>}avg(Price)*Sum (Qty)),ItemNumber, Type ))

I want to get the total value of consumed items for type 0 regardless of my type selection.

Sorry if I didn't gave you intitially enough details.

Thanks again,

Cheers,

Florian