Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Unveils New Agentic Capabilities Across Analytics, Data Engineering, and Trust: Learn More!
cancel
Showing results for 
Search instead for 
Did you mean: 
agigliotti

new customers trend over time

Hello,
I'd need to create a bar chart with:

- year quarter on X axis (ex. Q1 2026)

- number of new customers on Y axis (as customers who bought in actual period and didn't in previous)

example:
for Q1 2026 the value of Y axis should be calculated as number of customers who bought in Q1 2026 and didn't in Q4 2025 and so on...

The end user should be able to filter by product/product category and any other filters in the UI.

Then it can't be pre calculated in the script.

Could you help me to find the best way to achieve it?
Many thanks in advance for your time.

Best Regards

Labels (2)
16 Replies
WeLoveQlik
Partner - Creator
Partner - Creator

Are you looking to compare to just the prior quarter or any prior quarter?

Meaning If I made a purchase 2 years ago, should I be counted as a new customer this quarter, or no?

IPC Global: ipc-global.com
agigliotti
Author

in this case only the prior quarter, but I'll need also comparing with the same quarter previous year.

WeLoveQlik
Partner - Creator
Partner - Creator

I would do a check in the script.

First, load a distinct List of Customers and a Flag (I would use a 1) where Quarter = Prior Quarter.

Then, load a distinct list of Customers and a Flag where Quarter = Current Quarter.

Join the table together and if ISNULL(PriorQuarterCustomer) and CurrentQuarterCustomer >1 , New Customer, Existing Customer.

Each reload this check would be made and you should capture all new customers.

You could then use the New Customer flag in set analysis of your expressions.

IPC Global: ipc-global.com
marcus_sommer

I could imagine that the simplest approach would be not to use year-quarter as dimension else using n expressions (for each bar one) because it decoupled the periods from each other. Defining the current and previous one within appropriate set statements is rather simple - but in regard to the relationship of the data the current ones doesn't belong to the previous ones and revers and therefore they couldn't be compared directly.

UI alternatives may be to wrap the comparing logic within (n) aggr() to implement there approaches which ignore/overwrite the relationship with set statements like {1} and/or using interrecord-functions like above() to connect different periods with each other. Depending on real scenario such method could become quite complex.

Personally I would tend to transfer the essential part of the logic into the data-model by using The As-Of Table - Qlik Community - 1466130 or creating (n) flags containing the offset between the sales in day/month/quarters and/or between the sales and today and maybe some kind of clustering/scoring of these flags.  

agigliotti
Author

I forgot to say that the end user should be able to filter by product/product category and any other filters in the UI.
Then it can't be pre calculated in the script.

WeLoveQlik
Partner - Creator
Partner - Creator

using Product as an example. If they select product A, do you want to know if the customer purchased that particular product as a new customer this quarter vs last or any product?

IPC Global: ipc-global.com
agigliotti
Author

If they select product  A  then Q1 2026 should be calculated as the number of customers that purchased that product in Q1 2026 but didn't in Q4 2025.

WeLoveQlik
Partner - Creator
Partner - Creator

ok, great. It may take a little tweaking but something like this should work

Count(
If(
Sum({<YearQuarterIndex = {"$(vMaxQuarter)"}>} SalesAmount) > 0
AND
Sum({<YearQuarterIndex = {"$(vMaxQuarterM1)"}>} SalesAmount) = 0,
CustomerID
)
)

 

Your variables will have to ignore selections to ensure you are always getting the max quarter and Prior Quarter.

I suggest using an Index so at the turn of the year you can compare 2026Q1 and 2025Q4

IPC Global: ipc-global.com
agigliotti
Author

For the real scenario an UI approach is needed.
If it can be useful I can attach a small part of the fact table.