topic Re: Sum(Value1Value2)/Count(ProductValue2) in QlikView

Sum(Value1Value2)/Count(ProductValue2)

maxsheva — Fri, 08 Sep 2017 21:37:04 GMT

Hi,

How can be reached such kind of calculation?

Sum(Value*Age)/Count(Product*Age)

LOAD * Inline [

GroupName, Product,Age, Value

Type1, Product1, 30, 100

Type1, Product1, 30, 200

Type2, Product1, 40, 100

Type2, Product1, 40, 300

Type2, Product1, 40, 150

Type2, Product1, 50, 100

Type2, Product1, 60, 200

Type2, Product1, 60, 250

];

I need create expression where Age value can multiple each result from Sum(Value)/Count(Product)

Sum(Value*Age)/Count(Product*Age)

On the count side it is not working.

As output I need

For example Type2, Age 40

Sum(Value)=100+300+150=550

Count(Product)=3

Sum(Value)/Count(Product)=550/3=183.3

Then 183.3*40=7332

For Type2, Age 50

Sum(Value)=100

Count(Product)=1

Sum(Value)/Count(Product)=100/1=100

Then 100*50=500

For Type2, Age 60

Sum(Value)=200+250=450

Count(Product)=2

Sum(Value)/Count(Product)=450/2=225

Then 225*60=13500

Then Sum(7332+500+13500)/Sum(183.3+100+225)=41.96

Re: Sum(Value1Value2)/Count(ProductValue2)

Anil_Babu_Samineni — Sat, 09 Sep 2017 03:00:08 GMT

Are you sure about your expression?

Sum(Value*Age)/Count(Product*Age)

How Product and Age can multiply because Product is characteristic and Age is number?

How about this?

Sum(Value*Age)/(Count(Product)*Count(Age))

Re: Sum(Value1Value2)/Count(ProductValue2)

vishsaggi — Sat, 09 Sep 2017 03:50:19 GMT

Check this attached and let me know if this what you are looking for?

Your expressions should be:

= Count(Product)

= Sum(Value)

= Sum(Aggr((Sum(Value)/Count(Product)) * Aggr(Age, GroupName, Age), GroupName, Age))

= Sum(Aggr((Sum(Value)/Count(Product)), GroupName, Age))

= Sum(Aggr((Sum(Value)/Count(Product)) * Aggr(Age, GroupName, Age), GroupName, Age))/Sum(Aggr((Sum(Value)/Count(Product)), GroupName, Age))

Re: Sum(Value1Value2)/Count(ProductValue2)

antoniotiman — Sat, 09 Sep 2017 06:58:17 GMT

Hi Max,

maybe this

Sum(Aggr(Age*Sum(Value)/Count(Product),Age))/Sum(Aggr(Sum(Value)/Count(Product),Age))

Regards,

Antonio

Re: Sum(Value1Value2)/Count(ProductValue2)

rohitraut — Sat, 09 Sep 2017 12:05:15 GMT

Hello Max,

Please find the .QVW.

For example Type2, Age 40

Sum(Value)=100+300+150=550

Count(Product)=3

Sum(Value)/Count(Product)=550/3=183.3

Then 183.3*40=7332

For Type2, Age 50

Sum(Value)=100

Count(Product)=1

Sum(Value)/Count(Product)=100/1=100

Then 100*50=500 ""IT MUST BE 5000""

For Type2, Age 60

Sum(Value)=200+250=450

Count(Product)=2

Sum(Value)/Count(Product)=450/2=225

Then 225*60=13500

Then Sum(7332+500+13500)/Sum(183.3+100+225)=41.96

""""""Then Sum(7332+5000+13500)/Sum(183.3+100+225)=51"""".

Hope this help!!!!

Re: Sum(Value1Value2)/Count(ProductValue2)

maxsheva — Sat, 09 Sep 2017 22:20:29 GMT

Thanks all for reply.

Now I am on testing it.

topic Re: Sum(Value1*Value2)/Count(Product*Value2) in QlikView

Sum(Value1*Value2)/Count(Product*Value2)

Re: Sum(Value1*Value2)/Count(Product*Value2)

Re: Sum(Value1*Value2)/Count(Product*Value2)

Re: Sum(Value1*Value2)/Count(Product*Value2)

Re: Sum(Value1*Value2)/Count(Product*Value2)

Re: Sum(Value1*Value2)/Count(Product*Value2)

topic Re: Sum(Value1Value2)/Count(ProductValue2) in QlikView

Sum(Value1Value2)/Count(ProductValue2)

Re: Sum(Value1Value2)/Count(ProductValue2)

Re: Sum(Value1Value2)/Count(ProductValue2)

Re: Sum(Value1Value2)/Count(ProductValue2)

Re: Sum(Value1Value2)/Count(ProductValue2)

Re: Sum(Value1Value2)/Count(ProductValue2)