Measure produces negative result when Dimension filter added
Dear Qlik Experts,
To see how many policy sold and the premium amount in a given week, I used COUNT (policy) and SUM (premium) and associated with master Calendar’s time dimension.
Additionally, Policies get cancelled every week which have to be deducted from the aggregate COUNT (policy) and SUM (premium) for that week, regardless their production dates. Therefore, I simply added a field (Cancelled) populated 1 and one field (cancel_premium) to have the cancelled premium amount which are also associated with the Cancel date to the Master calendar.
However, when I add filter, i.e.; group_size or rep that are attributes of the policy, it gives me (negative) result, as COUNT (policy) is taking a subset of the policies sold for that week for that group or by one rep but my Cancelled and cancel_premium fields are not associated with those attributes but only Master Calendar’s dimension.
How can I also group Cancelled and cancel_premium fields by rep/ group size. I seek for your expert opinion and/or suggestion. Please help. Thank you very much for your time and effort.