Overdue model - how many times per year account on hold

[#1]

Hello all,

Hello all, I am trying to create a model where Qlik Sense would tell me how many times in one year a customer would have been on hold because of not paying their invoices on time. Both critera have to be met for a customer to be on hold:

• Oldest invoice > 'Stop Day' AND    (eg. 14 days)
• Overdue % at that date > 'Overdue Sensitivity' (eg. 5%)

The overdue sensitivity logic is there to prevent us putting customers on hold for very small values. In the full data  have 12 data points for the end of each month.

Test data set, 2 customer and 2 months

I have got this far so the measure in the last column is working correcty for a logic perspective, but then I want to create a dimention to group together those customers that pay perfectly, those that would go on stop once, 2 times, 3 times etc, and this is where I get stuck.

..so both customers 1 and 2 2ould be on stop at the end of Feb.

In the end I would like to use Qlik Branches SenseSankey extenstion to show how many customers went on stop by Total Receibales and Number of customers. The far right hand side would be number of times on stop (so up to 12 items), there would be other dimension that I would add to the customer, (for example customer segementation).

I have solved this in excel, and I have attached that along with the qlik app.

In the very end I will add variables to the UI using Qlik Branches Variable extension to model the impact of different rules in real time when presenting the final analysis.

Any help would be really appreciated, if more test data is needed I am more than happy to add it.

Kevin

[#2]

I have now made a full solution of what I am looking for in excel. I think if you follow the excel through the three sheets that probably explains faster than words can. I am looking to fully replicate this logic in Qlik Sense and there is a tab 'Final Outcome' that shows exactly what end result I am looking to get.

Thanks again.

Kevin