Solving function

[#1]

Hi everyone,

I do need a hand to solve this formula:

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

Thanks a lot

[passionate]

Hi Florian,

Please provide some sample data.

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

[passionate]

Hi Florian,

Try using below expression:

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

Regards,

Pankaj

[#4]

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

[passionate]

Hi Florian,

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

Regards,

Pankaj

[#6]

!Hi Pankaj, !

I had to log out and log in again to see your file.

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

[balar025]

Hi Florian,

Please share your sample data with your expected output to better understand your query .

[sunny_talwar]

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

[#9]

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

[sunny_talwar]

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

[#11]

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