Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
in this case only the prior quarter, but I'll need also comparing with the same quarter previous year.
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.
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.
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.
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?
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.
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
For the real scenario an UI approach is needed.
If it can be useful I can attach a small part of the fact table.