topic Re: Help...!! in QlikView

Help...!!

MK_QSL — Thu, 07 Aug 2014 12:16:59 GMT

I have table like below...

REGION	CUSTOMER	CREDIT	SALES
A	A02	2700	100
A	A02	2700	200
A	A03	3000	200
A	A06	2700	400
A	A08	3000	600
A	A08	3000	800
A	A08	3000	300
SH	SH03	1500	200
SH	SH08	4000	600
SH	SH10	4000	800
SH	SH10	4000	1100
SH	SH20	1500	1200
SH	SH20	1500	1400
SH	SH25	3500	1600

Need output table as below

REGION	CREDIT	SALES
A	11400	2600
SH	14500	6900

This solution should be on UI side only... I don't want to handle this on script end...

Re: Help...!!

tresB — Thu, 07 Aug 2014 12:19:13 GMT

Isn't it simple summation?

Dim: REGION

First Exp: Sum(CREDIT)

Sec Exp: Sum(SALES)

Re: Help...!!

MK_QSL — Thu, 07 Aug 2014 12:22:10 GMT

No Dear, Customer is Repeating and hence SUM(Sales) coming right but the Credit also getting counted more than once...

Re: Help...!!

tresB — Thu, 07 Aug 2014 12:25:14 GMT

Could you be a bit more clear on expected output logic? Is it that you want only the unique amounts to be summed up?

Re: Help...!!

maxgro — Thu, 07 Aug 2014 12:26:34 GMT

sum(aggr(sum(distinct CREDIT), CUSTOMER))

Re: Help...!!

giakoum — Thu, 07 Aug 2014 12:28:40 GMT

you can use the firstsortedvalue function to achieve that. If I understand well, records are repeated by credit, so you need sum of only one of the credit values

Re: Re: Help...!!

ashfaq_haseeb — Thu, 07 Aug 2014 12:29:13 GMT

Hi,

May be like this.

Regards

ASHFAQ

Re: Help...!!

MK_QSL — Thu, 07 Aug 2014 12:30:17 GMT

Yes, credit is repeating but need to count only once

Re: Help...!!

Thu, 07 Aug 2014 12:32:29 GMT

Dim REGION

Expr1: =sum(aggr(max(CREDIT),CUSTOMER))

Expr2: =sum(SALES)

You might need to add a column to identify which credit line to pick for each customer. Now it picks the one with largest value.

(Using max instead of distinct prevents summing two different credit values for the same customer if something is messed up in the data model)

Re: Help...!!

maxgro — Thu, 07 Aug 2014 12:32:39 GMT

Re: Help...!!

its_anandrjs — Thu, 07 Aug 2014 12:33:46 GMT

Use Distinct with sum

Dimension:- REGION

Expr1:- Sum(SALES)

Expr2:- Sum( Aggr( sum(DISTINCT CREDIT),CUSTOMER) )