topic Re: sum field after count distinct in QlikView

sum field after count distinct

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: sum field after count distinct

its_anandrjs — Fri, 29 Sep 2017 03:03:57 GMT

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

Re: sum field after count distinct

Anonymous — Fri, 29 Sep 2017 03:08:24 GMT

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

Re: sum field after count distinct

its_anandrjs — Fri, 29 Sep 2017 03:13:09 GMT

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.

Re: sum field after count distinct

Anonymous — Fri, 29 Sep 2017 03:28:27 GMT

HI! I have attached a sample file.

Re: sum field after count distinct

its_anandrjs — Fri, 29 Sep 2017 03:39:29 GMT

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

Re: sum field after count distinct

Anonymous — Fri, 29 Sep 2017 03:53:11 GMT

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

Re: sum field after count distinct

its_anandrjs — Fri, 29 Sep 2017 03:56:29 GMT

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

=Sum( DISTINCT QTY )

Re: sum field after count distinct

Anonymous — Fri, 29 Sep 2017 04:09:24 GMT

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

thanks!

Re: sum field after count distinct

Anonymous — Mon, 02 Oct 2017 06:46:02 GMT

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

Re: sum field after count distinct

its_anandrjs — Mon, 02 Oct 2017 07:29:17 GMT

Check this on your new thread

aggr