topic How to select data within a certain year, using user input? in App Development

How to select data within a certain year, using user input?

mcorser — Wed, 30 Mar 2022 16:08:46 GMT

I have a list of annual permit holders with a column indicating permit year. Each permit holder has an unique CustomerID that does not change from year to year (ie. customer 001 may have two records, one from 2019 and another from 2020, but with the same CustomerID). I want to have a user select a year, then see what percentage of permit holders renew their permits for the selected year. We'll call this "churn rate".

In the expression editor, how do I call the set of CustomerIDs for the selected year, and compare them to the set of CustomerIDs from the previous year?

Re: How to select data within a certain year, using user input?

Digvijay_Singh — Wed, 30 Mar 2022 16:54:35 GMT

May be like this -

Count({<Year={"$(=Max(Year))"}>}distinct [Customer ID])

Count({<Year={"$(=Max(Year)-1)"}>}distinct [Customer ID])

Re: How to select data within a certain year, using user input?

QFabian — Wed, 30 Mar 2022 17:03:54 GMT

Hi @mcorser . please try this :

demo data :

LOAD * INLINE [
CustomerId, Year
1, 2019
2, 2019
2, 2020
3, 2019
3, 2020
4, 2019
4, 2020
];

output expression :

count(CustomerId)
/
count({<Year = {"$(=Year-1)"}>} CustomerId)

demo output