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.

[Chanty4u]

Try this 

Count(DISTINCT

  {<

    TRADE_YEAR = {"=$(=Max(TRADE_YEAR))"}

  >}

  PAN_NUMBER *

  {<

    TRADE_YEAR = {"=$(=Max(TRADE_YEAR)-1)"}

  >}

  PAN_NUMBER

)

[BrunPierre]

=Count(DISTINCT {<TRADE_DATE={$(=Max(TRADE_DATE))}, PAN_NUMBER = p({<TRADE_DATE={$(=Max(TRADE_DATE)-1)}>} PAN_NUMBER)>} PAN_NUMBER)

[howdash]

To show count of distinct PAN_NUMBER for a year, you could write an expression like this:

Count(distinct PAN_NUMBER)

Then have a user select a year in the Year filter to show count of distinct PAN_NUMBER for selected year.

To show total count of PAN_NUMBER for a year, you would simply remove the distinct keyword from the expression, like this:

Count(PAN_NUMBER)

When user will select a year in a Year filter, they will get the total PAN_NUMBER for selected year.

To help you come up with an expression that calculates how many Customers repeated in current year, I would need more details on what you mean by "PAN_NUMBER will repeat as the customer comes again".

• Is there a CUSTOMER_ID field?
• Are values in PAN_NUMBER field supposed to be IDs that holds a representation of customer ID and an order number?
• Can a PAN_NUMBER value be the same for different customers?
• What is PAN_NUMBER?

Without understanding these details, it's hard to come up with accurate expression to answer the "how many customers repeated in current year?" and "how many customers from previous year were repeated in current year?" questions.

If you can provide some additional insight around what PAN_NUMBER is and how "a customer" is identified in the data, I can see what I can do to help with creating expressions to answer the last two questions.

[qliksenseadminthomascook]

Hello Howdash,

Thank you for your response. Please find the below requested information and feel free to ask if anything else is needed:

1. No separate Customer ID field is available.

2. PAN_NUMBER is the supposed customer ID which is alpha numerical and is unique to each customer.

Every time a customer visits, it will be recorded with PAN_NUMBER and TRADE_DATE.

So, lets say if a customer named 'Qlik1' with PAN_NUMBER 'ABC000' visited previous year for 5 times and current year for 8 times

customer named 'Qlik2' with PAN_NUMBER 'ABC011' visited previous year for 6 times and current year for 10 times,

customer named 'Qlik3' with PAN_NUMBER 'ABC024' visited only in current year for 9 times,

then we have to show:

RepeatCustomer	CY	PY_Repeat_in_CY
Qlik1	8	5
Qlik2	10	6
Qlik3	9	0

If we can get it from front end then it is okay and if we can get it from the script it will be even more helpful

Thanks in advance

[qliksenseadminthomascook]

Hello BrunPierre,

Thanks for the response. However, this is not working and it is giving null values.

[BrunPierre · Accepted solution]

Dimension 

Aggr(If(Count({$<TRADE_DATE = {">=$(=YearStart(Today(), -1))<=$(=YearEnd(Today(), -1))"}>} PAN_NUMBER) > 0, 'Repeat Customer', 'New Customer'), PAN_NUMBER)

Measures

CYV = Count({$<TRADE_DATE = {">=$(=YearStart(Today()))<=$(=Today())"}>} PAN_NUMBER)

PYV= Count({$<TRADE_DATE = {">=$(=YearStart(Today(), -1))<=$(=YearEnd(Today(), -1))"}>} PAN_NUMBER)

[howdash]

Script

Calculating current year's and last year's in the script can be done like this:

// load customers
customers:
Load *
Inline [
	Customer Name, PAN_NUMBER
    Qlik1, ABC000
    Qlik2, ABC011
    Qlik3, ABC024
]
;


// load visits log
customerVisitLog:
LOAD
    PAN_NUMBER,
    TRADE_DATE
FROM [lib://howdash/Qlik Community/customerVisitsLog.xlsx]
(ooxml, embedded labels, table is Sheet1)
;


// caclculate current year's visits per customer
aggregatedVisitLog:
Load PAN_NUMBER,
	Count(PAN_NUMBER)							as currentYearNumOfVisits
Resident customerVisitLog
Where Year(TRADE_DATE) = Year(Today())
Group By PAN_NUMBER
;


// caclculate last year's visits per customer
Left Join(aggregatedVisitLog)
Load PAN_NUMBER,
	Count(PAN_NUMBER)							as lastYearNumOfVisits
Resident customerVisitLog
Where Year(TRADE_DATE) < Year(Today())
Group By PAN_NUMBER
;

This will generate a data model like this with current and last year's counts available in the aggregatedVisitLog table:

Front-End Expressions

If you'd like to instead have the current and last year's values be calculated in the front-end, you can add the table object with either customer name or PAN_NUMBER as the dimension and the following two measures:

Current Year's Visits:

Count({<TRADE_DATE = {">=$(=YearStart(Today()))<=$(=YearEnd(Today()))"}>} TRADE_DATE)

Last Year's Visits:

Count({<TRADE_DATE = {">=$(=YearStart(AddYears(Today(), -1)))<$(=YearStart(Today()))"}>} TRADE_DATE)

This will create a table like this:

[qliksenseadminthomascook]

Dear Howdash,

Thank you for this response. This will give me the data of how many times the customer visited this year and how many times the customer visited in the previous year. However, what I am looking for is how many customers from previous year were repeated in current year and their count.

Thanks for your assistance.

[qliksenseadminthomascook]

Hello BrunPierre,

Thank you for your response. This is working really good. Can we use this for 3 to 4 years of data as well.

Like this is showing Current Year Count, Previous Year Repeated in Current Year Count.

Can I also see 2025 Count, 2024 Repeated in 2025 Count, 2023 repeated in 2025 Count, 2022 repeated in 2025 Count

Thanks