I am currently facing an odd problem in Qlik Sense.
I have 3 tables.
1. PERSON - containing information of a person
2. PRODUCT - whenever a person applies for a product, it will add a new row into this table. One row represents one product and PRODUCT is joined to PERSON. Hence, i would know which product belongs to who. The status of the product is stored as a column with indicators "Pending" or "Closed".
3. PRODUCT EXPIRY DATE - stores expiry date of a product. This table is connected to the PRODUCT table
Assuming there is only one type of product, how do i tell this: Out of all the pending PRODUCT applications, how many of these people applying for a new product has an existing product that is NOT expired. (In layman terms, if i owe a product that expires in a month's time, i may apply for the product again. I have to know out of all these product applications, how many people actually stopped using this product for a period of time before applying for it again)
I am aware of the basic functions and am capable of counting the number of products which are expired. However, keep in mind that a person may have used this product many times. If i used this product for 5 times, i will have 5 rows tagged to my name in the PRODUCT table.
My current idea to approach this problem is to create another table which shows the personname and the latest expiry date.
If my explanation is not clear, i will be able to further elaborate based on your questions.