Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arielrodrigues
Contributor II
Contributor II

Sum Distinct Invoice Numbers

Hey guys.

I have a sheet (attached) that brings me the invoices, quantity sold of the item, and the barcode of them.

In that sheet, it brings me repeatedly the total of items sold in that invoice and if I sum it, it goes wrong.

There are different invoices with the same count of items sold. The "DISTINCT" takes it out of the sum. 

In the below table, "Qtde. Faturada" refers to SUM(DISTINCT IF(NUMNFV,QTDFAT)).

The total row at the top shows 2.950, but it needs to show 5.170

arielrodrigues_0-1627936240448.png

I only get the satisfying result if I change the way this, from "Total of Expression" to "Sum rows"

arielrodrigues_2-1627936648729.png

To make a chart after that, it's the same problem. Does anyone have an answer to that?

 

Ariel Santiago Rodrigues
Assistente de Gestão e Controle
1 Solution

Accepted Solutions
Vegar
MVP
MVP

Maybe an aggregated aggregation is what you are trying to to do. Take a look at this example.

Vegar_0-1627973301310.png

 

The expression I've used is: SUM(aggr(SUM(distinct QUANTITY),INVOICE,[PRODUCT CODE]))

I where not you able to reproduce the figures  5.170 your Sheet.xlsx as a data source? If my suggestion above does not solve your issue then please provide a load script that reads your excel in a correct manner. For my solution I did this data model.

Vegar_1-1627973446661.png

 

View solution in original post

1 Reply
Vegar
MVP
MVP

Maybe an aggregated aggregation is what you are trying to to do. Take a look at this example.

Vegar_0-1627973301310.png

 

The expression I've used is: SUM(aggr(SUM(distinct QUANTITY),INVOICE,[PRODUCT CODE]))

I where not you able to reproduce the figures  5.170 your Sheet.xlsx as a data source? If my suggestion above does not solve your issue then please provide a load script that reads your excel in a correct manner. For my solution I did this data model.

Vegar_1-1627973446661.png