topic Re: Average selling price Calc in QlikView

Average selling price Calc

AH — Tue, 13 Jan 2015 07:49:04 GMT

Hi,

I have a requirement like this: Calculate Average selling Price of Instruments. I am using expression like this but its giving me wrong Average.

=num(Sum({<ItemID={'XT, GT'}>} [TotalLineAmountInReportCurrency])/Count({<ItemID={'XT', GT},Qty>}Distinct InvoicesourceCustId),'$##,##0')

I am Having Problem putting the Qty in the expression. But i have to count/sum the Qty to get the average price.

Count/sum for quantity? Any feedback would be a great help.

Thnaks,

Shan

Re: Average selling price Calc

Tue, 13 Jan 2015 07:58:57 GMT

use avg(aggr(yourexpression,fields))

Re: Average selling price Calc

anbu1984 — Tue, 13 Jan 2015 08:00:00 GMT

Are you trying to do this calc?

(Sum(Amt)/Count(CustID))/ Sum(Qty)

Is XT and GT two different ItemIDs? Then add quotes around each ItemID like below

=num(

(Sum({<ItemID={'XT', 'GT'}>} [TotalLineAmountInReportCurrency])/Count({<ItemID={'XT', 'GT'}>}Distinct InvoicesourceCustId)) / Sum({<ItemID={'XT', 'GT'}>}Qty)

,'$##,##0')

Re: Average selling price Calc

AH — Tue, 13 Jan 2015 08:04:06 GMT

Hi Santosh,

Thanks for your reply but I dont get it what you said.

Thanks,

Shan

Re: Average selling price Calc

AH — Tue, 13 Jan 2015 08:04:39 GMT

The expression looks convincing but its not working.

Thanks,

Shan

Re: Average selling price Calc

anbu1984 — Tue, 13 Jan 2015 08:09:45 GMT

Can you post sample data and expected output

Re: Average selling price Calc

AH — Tue, 13 Jan 2015 08:30:30 GMT

Hi Anbu,

Thanks for your reply.

I need to calculate the average for (CCP and CSA combination) , and (X1 and X25 Combination) . Please find the attach data file.

The expected average for 1st combi should be around 1500 and for the second combo should be around 5000. But Thats just an idea i got from BA. Its not the exact figure any ballpark range should be fine if the calculation is ok.

Thanks,

Shan

Re: Average selling price Calc

Tue, 13 Jan 2015 09:46:27 GMT

Hi ahmed

quick question, what does Qty measures as I would assume that we looking at quantity of items, but you have negative values in your data source, Once I understand that I bliv we can come up with an appropriate formula

Kind Regards

Dlamini

Re: Average selling price Calc

AH — Tue, 13 Jan 2015 09:49:19 GMT

Hi Dlamini,

Qty is the Quantity. Dont worry about the negative amount. Just consider them as Just number.

Thanks,

Shan