topic Expression for New Customers Each Month in App Development

Expression for New Customers Each Month

datagrrl — Wed, 10 Jan 2024 15:48:09 GMT

I searched the community for this and asked some of my colleagues, but was not able to get an answer.

I know I could create a flag on the import script, but this is a large dashboard used by multiple teams. I don't want to change to script for one project out of hundreds. I will do it if it is the only option.

Is there a was to get in the expression new customers monthly?

My data is pretty simple:

CustomerID and SalesDate

I want a bar chart for new customers monthly, basically using the customers minimum sales data as the month they are considered a new customer.

Right now I am just counting the unique customers, because I have tried a few things and nothing has worked.

The results should be closer to this.

Any assistance is appreciated. I have attached a mocked up app.

Re: Expression for New Customers Each Month

NellyAcko — Wed, 10 Jan 2024 16:51:23 GMT

If you load your data in order by CustomerID and SalesDate and then use

AutoNumber(CustomerID&SalesDate) as IDkey

In your Chart use SalesDate and IDkey, where the expression for the IDkey is for only values that = 1 which will be the 1st time a new CustomerID is present.

This will give you the desired result.

Re: Expression for New Customers Each Month

Scotchy — Fri, 12 Jan 2024 02:56:00 GMT

A possible approach given your question...

To create a bar chart for new customers monthly in Qlik Sense, using the customer's minimum sales date as the month they are considered a new customer, you'll need to employ a combination of Qlik Sense's date functions and aggregation functions. The key here is to identify the first purchase date for each customer and then count the number of new customers for each month.

Here's a step-by-step approach:

Load Your Data: Ensure your data is loaded with the CustomerID and SalesDate fields.
Create a Field for First Purchase Date: You'll need to create a new field in your data model that represents the first purchase date for each customer.
Aggregate New Customers Monthly: Use an expression in your bar chart to count the number of unique customers who made their first purchase in each month.

Here's an example script and expression to guide you:

Script to Create a Field for First Purchase Date:

sql

LOAD
    CustomerID,
    SalesDate,
    ...
;

LOAD 
    CustomerID,
    Min(SalesDate) as FirstPurchaseDate
FROM [YourDataSource]
GROUP BY CustomerID;

In this script, Min(SalesDate) calculates the first purchase date for each customer.

Expression for Bar Chart:

In your bar chart, you'll need an expression to count the number of new customers for each month. The dimension of your bar chart should be the month and year of the FirstPurchaseDate, and the expression should count the number of unique CustomerIDs.

For the dimension, use:

qlik

Month(FirstPurchaseDate) & '-' & Year(FirstPurchaseDate)

For the expression, use:

qlik

Count(DISTINCT CustomerID)

This setup will give you a bar chart where each bar represents a month, and the height of the bar represents the number of new customers for that month.