Customer Retention - error in Set Expression using p() to determine customers with jobs last year, but no jobs this year

[Melissa5]

Need help with my formula to compute Lost Jobs by Customer.  In the screenshot of my table below, I am trying to count the # of jobs performed in 2021 for customers  who had no jobs  performed in 2022 .  But instead of getting only 2021 jobs, the count is showing me all jobs performed in ALL years (where the customer had no jobs in 2022).  So like on the first row where there were 11 jobs in 2021, my Lost Jobs formula should return 11 and not 16.

count({$<customer = p({$<JobDueDate={">=01/01/2021<=12/31/2021"}>}) - p({$<JobDueDate={">=01/01/2022<=12/31/2022"}>}customer)>} job_id)

[Oleg_Troyansky · Accepted solution]

Hi Melissa,

Your formula is working as intended, even if it's not producing the results that you expected. Here is why. 

Your Set Analysis filter is selecting customers (not jobs)with Job dates in 2021 and not in 2022. These same customers may have other jobs in other years too, So, in order to only count # jobs in 202 2, you should add one more filter on the filed date and limit the JobDates by 2021, outside of the function P().

To learn more about using advanced Set Analysis, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

[Melissa5 · Accepted solution]

Thank you.  But I am so new to this, I don't understand how to modify my formula with this additional filter.  Can you please show me how to correct my  formula?

[Melissa5]

Wait, I read your reply closer.  You are saying to add a filter to the table itself, not to add something to my formula.  I've now added a filter to my table and my formula works!  Thank you so much!!!!