The Sum of a dimension field based on unique (distinct) dimension
I am trying to calculate the sum of one of my dimension fields and show it in a text box as a KPI. Simple enough.
The issue is I need it to be distinct based on another field. Here is my Situation:
Sum of # of days Late for Product A & B. Lets say this should be 50 days late. Product A is 15 and Product B is 35.
Product A has multiple rows because product A has differing ingredients e.g. Sugar, salt, milk, flour etc and each of those rows is listed as 15 days late because they roll into Product A which is 15 days late.
However, in Qlik the result I am getting is 95 (15 for Product A - Salt, 15 for Product A - milk, 15 for Product A - Sugar, 15 for Product A - flour and 35 for Product B).
This is not the true case, it should only be 50. Is there a way to create a distinction on how the calculation is to be made?
I hope I have illustrated this well and any help rendered will be truly appreciated.