How to create a count of years per sales customer in Qlik Sense?

[jbchurchill]

 

My Screenshot shows a table in Qlik Sense. I've got CustomerId and a field representing a year. Repeat customers will have their CustomerID repeating in that CustomerID column and sometimes the year is null for a CustomerID (because the year is the year something was applied rather than the year of the transaction; think of it as a gift card purchase transaction but I'm only interested in when it was applied)

Question: How can I count the number of years across each customer? I keep picturing a matrix with a year in columns and customers in rows and just a binary 0, 1 in the cells to represent that an application was made for that year and then with a summary column at the end (like a CountIF in Excel or with Sum as a pandas crosstab aggFunc. to sum all the "1"s across each row).

I keep thinking of a couple of ways I could represent this (and I might want to explore multiple things). Something like bins of the number of years applied could get me bars showing numbers (or percentages) of customers with less than 5 years of applied sales, 5-10, 10-15, etc. 

[Or]

There doesn't seem to be anything attached, but what you describe sounds like you should have a table with Customer and then a measure of Count(distinct Year)

[jbchurchill]

Thanks - I added the attachment. Yes I have CustomerID and the Year field. Making it a Count seemed like the easy answer but I couldn't (at first) seem to do that with the table until I re-read the important point that you provided "it needs to be a measure".  I think this was the answer (once I added "distinct" to the count). I think I can make the charts I need. This turned out to be much simpler than I anticipated so Thank You!

[jbchurchill]

Actually I spoke too soon ? ... (I actually already had that). I wanted to take it further. I'm trying to get averages for the numbers of years customers repeat. So for each customer, how many years did they participate in at least one transaction (so it needs to track each customer across every year and sum that up). When I have this I can know how many customers have purchased only once vs. those that have made multiple purchases. Does that make sense?

I already have this (attached).
Even though they are distinct customers per year, I do not have a count of the years each customer has purchased.

[Or · Accepted solution]

If you're looking for the average number of years that customers repeat, you would need something along the lines of:

avg(aggr(Count(distinct Year),Customer))

The aggr() will iterate the inner expression of overall customers, and the result will be averaged.

[jbchurchill]

I think I just need to bin this one. Your careful description of the dimension and measure got me thinking I just need to reverse so now I have the number of years (count of years) by customer (dimension). I'm getting the broken bars (outside of the range of expected values?) because it appears the data repeats the customer ID for different products. 

 

[Or]

That is why you need to count distinct, rather than a flat count - otherwise you will count each year multiple times if it appears in multiple rows of your data.

In any case, if you want to bin this and use it as a dimension, you would presumably need some sort of aggr(SomeFormulaHere,Customer) so that the bins are aggregated per customer.