Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
mja_jami
New Contributor III

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.

aggregate wo filter.PNG

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.

Filter by Groubsize subset.pngDataModelCancel.PNG

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.

Best regards,

Jami