Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
MVP
MVP

Help...!!

I have table like below...

REGIONCUSTOMERCREDITSALES
AA022700100
AA022700200
AA033000200
AA062700400
AA083000600
AA083000800
AA083000300
SHSH031500200
SHSH084000600
SHSH104000800
SHSH1040001100
SHSH2015001200
SHSH2015001400
SHSH2535001600

Need output table as below

REGIONCREDITSALES
A114002600
SH145006900

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

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

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

View solution in original post

10 Replies
Highlighted
MVP
MVP

Isn't it simple summation?

Dim: REGION

First Exp: Sum(CREDIT)

Sec Exp: Sum(SALES)

Highlighted
MVP
MVP

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

Highlighted
MVP
MVP

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

Highlighted
MVP
MVP

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

View solution in original post

Highlighted
Partner
Partner

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

Highlighted

Hi,

May be like this.

Regards

ASHFAQ

Highlighted
MVP
MVP

Yes,  credit is repeating but need to count only once

Highlighted
Not applicable

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)

Highlighted
MVP
MVP

1.png