Need to find the repeat count of PAN from previous year in current year

[qliksense1thomascook]

Hi all,

I have two fields, PAN_NUMBER and TRADE_DATE. PAN_NUMBER will repeat as the customer comes again. I want to show the count of Distinct PAN_NUMBER for a year, Total count of PAN_NUMBER for a year, How many Customers repeated in current year, How many customers from previous year were repeated in current year.

Any help or idea is appreciated

Thanks in advance.

[howdash · Accepted solution]

Oh good catch! I've created a video showing how to find repeat customers for any year that you would like. Video is below.

The short answer is that you will need more than just an expression to make this work.

You would need to create:

• An alternate state
• A Trade Year dimension in the data model
• Couple of filters for the Trade Year dimension in the default and alternative state
• And expressions that will identify repeat customers for trade years that user selects

An example of an expression that you would create to identify repeat customers in this setup would be something like this:

If(Count(transactionId) > 0 and Count({alt} transactionId) > 0, Count(transactionId), 0)

and this one:

If(Count(transactionId) > 0 and Count({alt} transactionId) > 0, Count({alt} transactionId), 0)

Once you have the alternate state created and filters added, these two expressions will generate a table like this:

The users will then be able to select any trade year and a comparison trade year and see the repeat customers for selected years.

For reference, attached is the app and the spreadsheet that I've used in the video.

[BrunPierre · Accepted solution]

You may need to create separate measures for that. For instance, for customers from 2022 that also appear in 2025, you could do something like this:

=Count(DISTINCT {
<TRADE_DATE = {">=$(=YearStart(Today()))<=$(=YearEnd(Today()))"},
PAN_NUMBER = P({<TRADE_DATE = {">=$(=YearStart(AddYears(Today(), -3)))<=$(=YearEnd(AddYears(Today(), -3))"}>} PAN_NUMBER)
>} PAN_NUMBER)