Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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é
@sunny_talwar Do you maybe have an ideam, how to solve this? 🙂 would be awesome
Try this
Aggr(Sum(DISTINCT [Monthly Costs]), [Contract ID], [Year-Month])
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?
not sure I follow.... do you have a sample we can look at?