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: 
Not applicable

Sum of Sales where ID is DISTINCT

Hello,

I have the following table:

ID,Sales

1, 200

1, 200

2, 200

3, 200

In the above table how would I get the sum of Y where ID is DISTINCT?

Thanks!

-Jason

1 Solution

Accepted Solutions
Not applicable
Author

Massimo, thanks for the prompt response! I should have stated that the table goes on much longer and contains multiple records with the same ID... I figured out how to do it via browsing more posts using the AGGR function:


sum(aggr(sum(DISTINCT [Sales]), [ID]))

-Jason

View solution in original post

7 Replies
maxgro
MVP
MVP

do you want to exclude 1 (no distinct)?

if(count(ID)=1, sum(Sales))

or sum the sales of 1 only once?

sum(DISTINCT Sales)

1.png

Not applicable
Author

Massimo, thanks for the prompt response! I should have stated that the table goes on much longer and contains multiple records with the same ID... I figured out how to do it via browsing more posts using the AGGR function:


sum(aggr(sum(DISTINCT [Sales]), [ID]))

-Jason

Not applicable
Author

Si estás creando un objeto de tipo gráfico es sencillo:


1. El id lo adicionas a una dimensión

datos.jpg

2. Creas una expresión así:

datos.jpg

3. Resultado:

datos.jpg@

pratap6699
Creator
Creator

yes...that expression is correct....here aggr() works like a group by clause in SQL,,,so aggr(sum(DISTINCT sales),ID)

here ID wise sum(sales) displayed in chart

Naveen
Contributor
Contributor

Data:

Document NumberAmountMonthWeek
110Jan1/5/2018
220Jan1/5/2018
330Jan1/12/2018
440Jan1/12/2018
110Feb2/2/2018
330Feb2/2/2018
330Feb2/9/2018
550Feb2/9/2018
630Feb2/9/2018

 

I tried to Calculate the sum of amount for distnct Document Nember group by Month week individually

the expression works fine

( =Sum(Aggr(Sum(distinct Amount),[Document Number],Month)) )

MonthAmount
Jan100
Feb120

=Sum(Aggr(Sum(distinct Amount),[Document Number],Week))

WeekAmount
1/5/201830
1/12/201870
2/2/201840
2/9/2018110

 

but if i tried for both(Month & Week ) together in pivot table the expression is not working good

=Sum(Aggr(Sum(distinct Amount),[Document Number],Month,Week))

MonthWeekAmount
FebTotals150
2/2/201840
2/9/2018110
JanTotals100
1/5/201830
1/12/201870

 

Will  you please let me know how to find sum of sales for distinct field group by multiple fields

 

Anil_Babu_Samineni

What you want to see?
And don't create Dump https://community.qlik.com/t5/Qlik-Sense-Cloud-Discussions/Sum-of-Sales-for-distinct-Document-number...
Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
AmitKakkad
Contributor III
Contributor III

Brilliant! Exactly what I was looking for. My respect to anyone who uses aggr like this! 🙂