topic Re: Distinct Aggregated Sum by Date in App Development

Distinct Aggregated Sum by Date

MateP — Sat, 16 Nov 2024 19:28:51 GMT

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é

Re: Distinct Aggregated Sum by Date

MateP — Wed, 11 Dec 2019 13:28:49 GMT

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

Re: Distinct Aggregated Sum by Date

sunny_talwar — Wed, 11 Dec 2019 13:44:12 GMT

Try this

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

Re: Distinct Aggregated Sum by Date

MateP — Wed, 11 Dec 2019 14:23:00 GMT

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?

Re: Distinct Aggregated Sum by Date

sunny_talwar — Wed, 11 Dec 2019 14:28:07 GMT

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