Qlik Community

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
MVP

Re: Solving function

What is the problem you are facing?

agigliotti
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

agigliotti
Honored Contributor II

Re: Solving function

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

passionate
Valued Contributor

Re: Solving function

Tis might be silly but try

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


Regards,

Pankaj

passionate
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

roharoha
Valued Contributor III

Re: Solving function

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

Not applicable

Re: Solving function

Thank your all for your reactivity and your help, I do really appreciate it.

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