Distinct Aggregated Sum by Date

[MateP]

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 ID	Monthly Cost
1	100
1	100
2	500
3	800

TimeTable:

Contract ID	Year+Month (YYYYMM)
1	201911
2	201911
1	201912
2	201912
3	201912

Expected Sums per Month:

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

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

Thanks and BR
Máté

[MateP]

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

[sunny_talwar]

Try this

Aggr(Sum(DISTINCT [Monthly Costs]), [Contract ID], [Year-Month])

[MateP]

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?

[sunny_talwar]

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