sum field after count distinct

[Anonymous]

Hello Qlikers!

Need help what  formula do I need for me to get the correct QTY  field.

correct QTY is derived by dividing QTY over # of LOTS per LOT TITLE.

I have done  sum(QTY) / # of LOTS but the result is 10639

or sum(QTY) / count(DISTINCT LOTS) = 23222.6

What I am after is the total of the correct QTY which is 168,649

LOTS	# of LOTS	QTY	correct QTY
LOT 1	2	26,098	13,049
LOT 2	2	2,890	1,445
LOT 3	2	35,486	17,743
LOT 4	2	37,396	18,698
LOT 5	2	25,284	12,642
LOT 6	1	27,834	27,834
LOT 7	1	12,658	12,658
LOT 8	1	27,810	27,810
LOT 9	1	28,940	28,940
LOT 10	1	7,830	7,830
TOTAL	15	232,226	168,649

thanks and hope I have explained my problem in detail.

[its_anandrjs]

Data that you provided it is latest and correct or it form the chart you provided?

[Anonymous]

sorry I did not get your question but let me try to explain.

the data is a sample of the pivot chart except for the correct QTY field.

so LOTS, # of LOTS & QTY are part of pivot table, correct QTY field is a manual computation.

thanks

[its_anandrjs]

Ok i have to ask how you calculate  # of LOTS & QTY and you have difficulty in calculate the QTY or may be provide and sample for this.

[Anonymous]

HI! I have attached a sample file.

[its_anandrjs · Accepted solution]

You have to use DiSTINCT or you have duplicate values for the single lots is this your data is correct

In Expression use

=Sum(Aggr(QTY,LOTS))

See my attached also

[Anonymous]

HI Anand, thank you! this is the expression I need.

[its_anandrjs]

You can use this also but carefully you have to do this.

=Sum( DISTINCT QTY )

[Anonymous]

have tried it but =Sum(Aggr(QTY,LOTS)) is much closer

thanks!

[Anonymous]

Hi Anand,

I would like to make  a follow up question on the formula.

Where can I insert the week in order for the data to reflect the current week only.

below formula is giving me a zero data

Sum({<Fiscal_Week = {"$(=MaxString(Fiscal_Week))"}>}Aggr(QTY,LOTS))

thanks