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)

[Elemy91]

Great video! I learned a lot from this.

What about creating a KPI that immediately shows how many customers meet the definition of "repeat customers" for the selected years?

In your example, you only have three customers in your database, but in a larger one it’s not easy to scroll through or count from a table.

Also, your expressions seems to work only when the customer is used as a dimension, but as an analyst I’d appreciate a measure that can also be used for statistics — for example, a chart showing how many customers are "repeat customers" year by year.

What would you suggest?

[qliksense1thomascook · Accepted solution]

Dear Elemy,

Please check the below expression which I am currently using. This can be used in KPI's, bar charts and tables as well.

If you are concerned only with the previous year, you can use the below expression:

Count ( distinct

{<

Trade_Year = {"$(=vMaxYear)"},

PAN_NUMBER = p({<Trade_Year = {"$(=vPrev1Year)"}>}) 

>}

PAN_NUMBER

)

The below expression will give you the customers who came in previous year and current year but not two years before.

Count ( distinct

{<

Trade_Year = {"$(=vMaxYear)"},

PAN_NUMBER = p({<Trade_Year = {"$(=vPrev1Year)"}>}) 

                            *

                            e({<Trade_Year = {"$(=vPrev2Year)"}>})

>}

PAN_NUMBER

)

If you want the customers in three consecutive years:

Count ( distinct

{<

Trade_Year = {"$(=vMaxYear)"},

PAN_NUMBER = p({<Trade_Year = {"$(=vPrev1Year)"}>}) 

                            *

                            p({<Trade_Year = {"$(=vPrev2Year)"}>})

>}

PAN_NUMBER

)

Hope this is helpful to you.