New customers - line chart - set analysis

[qlikeers]

Is it possible to graph only new customers by month using broad transactional data (fact table plus dimension tables), without using the load editor and using only set analysis? A new customer is one who bought for the first time in a given month and has never bought anything before.

[vighnesh_gawad · Accepted solution]

yes, it is possible in Qlik Sense using only set analysis. Check if this expression solve your query.

Count(
    DISTINCT
    Aggr(If(
            MonthStart(Min(TOTAL <CustomerID> TransactionDate))
            = MonthStart(TransactionDate),
            CustomerID
        ),
        CustomerID,
        MonthStart(TransactionDate)
    ))

[qlikeers]

Thanks, it seems to show the correct numbers after some adjustments.

I just have one more question: can this be written so that it works correctly also depending on the selection of different dimensions?

[vighnesh_gawad]

Can you expand on that? The current expression will work based on selections. Right now, it evaluates against the entire dataset, but when users select any dimensions, the expression will recalculate only for the selected data subset.

Do you want any specific behavior when someone clicks on a dimension?

[qlikeers]

Currently, the model looks like a star: there's a fact table (transactions) and several dimension tables, such as those for products and calendars.

I'd like it to calculate correctly regardless of the dimension I select in the filter. But I think it might actually work that way now that I think about it.

[qlikeers]

How would you calculate:
1) Regular customers in the last 12 months? One who buys at least once in each of the last 12 months.

This can be done for both a single KPI and a line chart by month (for the last 12 months).

2) Lost customers in the last 6 months? They bought 7 months ago and haven't bought in the last 6 months.

This can be done for both a single KPI and a line chart by month (for the last 12 months).

Preferably, full months, not individual dates, although this is also an option.

[vighnesh_gawad]

Hi @qlikeers , it would be better to create a new thread for new questions and close this one by marking solution.

I think both of your KPIs should be possible with similar approach to my previous solution, but it may need a bit of testing and some additional thought.