19 Replies Latest reply: Aug 8, 2017 8:44 AM by Florian Saner

# 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

• ###### Re: Solving function

What is the problem you are facing?

• ###### 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

• ###### Re: Solving function

you can't use Sum inside Avg.

maybe this:

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

• ###### 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

• ###### Re: Solving function

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

• ###### 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

• ###### Re: Solving function

Tis might be silly but try

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

Regards,

Pankaj

• ###### Re: Solving function

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

• ###### 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

• ###### Re: Solving function

Hi Florian,

In below formula you only want to add a set analysis modifier i.e Type=0 ?

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

Regards,

Pankaj

• ###### Re: Solving function

Hi Florian,

Try using below expression:

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

Regards,

Pankaj

• ###### Re: Solving function

Hi Pankaj,

Yes That's what i'm trying to do but I don't understand why it doesn't work out.

The correct answer is 53.07 (Qlik Sense with filter: year=2017 and type=0) and while using my function I get 11.45 (filter: year=2017)

It's quite hard to provide you with some data because they are confidential.

Best,

Florian

• ###### Re: Solving function

Hi Florian,

I have attached a QVW please check if this is what you want.

Regards,

Pankaj

• ###### Re: Solving function

!Hi Pankaj, !

I would need to get 3875 for your example.

 ItemNumber Price Qty Type Total cost ItemNumber AVG(Price) Total 1 100 1 0 100 1 181.8182 CHF 182 1 100 3 0 300 2 468.75 CHF 545 1 200 1 0 200 3 172.2222 CHF 182 1 200 4 0 800 4 392.8571 CHF 727 1 300 2 0 600 CHF 364 2 100 4 0 400 CHF 1 875 KPI Value CHF 3 875

Firstly, I have calculated the weighted price in the AVG(Price) column and then I have multiplied the quantity corresponding to 0 type with this weighted price to get CHF 3875.-

Let me know if it's not clear.

Best,

Florian

• ###### Re: Solving function

Hi Florian,

• ###### Re: Solving function

Everything in your chart seems to be Type 0? Is that a typo?

• ###### Re: Solving function

I added a filter for the type in Excel.

If I refer to the data given by Pankaj I would like to get this:

 ItemNumber Price Qty Type Total cost ItemNumber AVG(Price) Total 1 100 1 0 100 1 181.8182 CHF 182 1 100 3 0 300 2 468.75 CHF 545 1 200 1 0 200 3 172.2222 CHF 182 1 200 4 0 800 4 392.8571 CHF 727 1 300 2 0 600 CHF 364 2 100 2 1 200 2 100 4 0 400 CHF 1 875 2 100 5 1 500 2 200 2 1 400 2 2000 3 1 6000 KPI Value CHF 3 875 3 100 4 2 400 3 100 5 2 500 3 200 5 2 1000 3 300 4 1 1200 4 100 2 3 200 4 100 3 2 300 4 100 4 3 400 4 200 3 3 600 4 2000 2 2 4000
• ###### Re: Solving function

But how are you getting the values for TOTAL column? Can you share an excel file showing the calculation used here?

• ###### Re: Solving function

We do have 6 items with type = 0 which should appear in my answer and they are displayed within the total column.

Type, Qty, AVG(Price), Total (Qty*Price)

1 ,1, 181.8182, 182

1, 3, 181.8182, 545

1, 1, 181.8182, 182

1, 4, 181.8182, 727

1, 2, 181.8182, 364

2, 4, 468.75, 1875

Best,

Florian