Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
MateP
New Contributor II

Distinct Aggregated Sum by Date

Hi everyone, 

I am facing a problem with summing the values of my "Monthly Costs" field in Qlik Sense. I have a Bar Char where the Stacks are Dates in the format YearMonth from the "TimeTable" and the Height is supposed to be the sum of "Monthly Costs" per YearMonth. 

However, I have quite a few duplicates (which I need to keep) in the table "ContractTable" where my Contract IDs and Monthly Costs are and therefore, when using the simple Sum([Monthly Costs]) function a lot of them get summed more than once.

I have tried to use Aggr(distinct Sum([Monthly Costs]),[Contract ID]). The values however are far too little compared to the expected sum.

Sample Data:
ContractTable:

Contract IDMonthly Cost
1100
1100
2500
3800

 

TimeTable:

Contract IDYear+Month (YYYYMM)
1201911
2201911
1201912
2201912
3201912

 

Expected Sums per Month:

Year+Month (YYYYMM) (X-axis)Sum of Costs (Y-axis)
201911Contract1+Contract2=100+500=600
201912Contract1+Contract2+Contract3=100+500+800=1400

 

I would be very grateful, if someone could suggest a solution.


Thanks and BR
Máté

Labels (3)
4 Replies
Highlighted
MateP
New Contributor II

Re: Distinct Aggregated Sum by Date

@sunny_talwar Do you maybe have an ideam, how to solve this? 🙂 would be awesome

Highlighted

Re: Distinct Aggregated Sum by Date

Try this

Aggr(Sum(DISTINCT [Monthly Costs]), [Contract ID], [Year-Month])
Highlighted
MateP
New Contributor II

Re: Distinct Aggregated Sum by Date

Thanks but it did not quite work as expected, yet. 
If I have a "Bar" defined in the barchart as well which shows the partner company's names with different colors..could that have an effect on the total sums?

Highlighted

Re: Distinct Aggregated Sum by Date

not sure I follow.... do you have a sample we can look at?