Monthly customers KPIs - selected month KPI and line chart

[qlikeers]

Hi!
I'd like to calculate a few metrics that will count customer numbers. I'd like to display this in 1) a KPI for the month selected in the filter, defaulting to the current month, and 2) a line chart for the last 12 months, meaning 12 data points on the chart.

I have a unaggregated star model. There's a purchase date, purchase month, items, transaction value, transaction number, etc.

This requires that I do this in Set Analysis, preferably Master Masures. I don't want to change anything in the script, so we're assuming it's done on the front end. I'd like it to work with every possible filter selection.

KPIs:
1) Regular customers - they buy at least once every month - we analyze the last 6/12 months,
2) Lost customers - they bought 4 months ago and haven't bought in the last 3 months,
3) Recovered customers - they bought in the current month and haven't bought in the last 3 months,
4) Customers with positive sales dynamics year-over-year - this means that by default, we use the current year and compare sales year-to-date with the same period the year before, counting those with higher current sales. If we select earlier years, compare the entire year to the entire previous year.
5) Customers falling into specific 5 sales ranges each month. This means we count how many customers in a given month bought in ranges A, B, C, D, or E. For example, we assume A <=500, B <501-1000>, etc.

I have a few ideas, but I'd like to do it efficiently and in the shortest possible code.

Maybe you can suggest something? It's always worth comparing 🙂

[Chanty4u]

Hi may be this .

Current month .

Count({<

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

>} DISTINCT CustomerID)

Line chat last 12 months .Count({<

PurchaseMonth={">=$(=AddMonths(Max(PurchaseMonth),-11))<=$(=Max(PurchaseMonth))"}

>} DISTINCT CustomerID)

Regular customer example for 6 months .Count(

Aggr(

If(Count(DISTINCT PurchaseMonth)=6, CustomerID),

CustomerID

))

Lost customers

Count(

{<

CustomerID =

P({<PurchaseMonth={"=$(=AddMonths(Max(PurchaseMonth),-4))"}>} CustomerID)

-

P({<PurchaseMonth={">=$(=AddMonths(Max(PurchaseMonth),-3))"}>} CustomerID)

>}

DISTINCT CustomerID

)

Current year vs previous year 

Count(

Aggr(

If(

Sum({<Year={"$(=Max(Year))"}>} SalesAmount) >

Sum({<Year={"$(=Max(Year)-1)"}>} SalesAmount),

CustomerID

),

CustomerID

))

[qlikeers]

@Chanty4u thanks for your answer, but ...

I don't think it will work this way, because these measures need to operate on specific date ranges, dynamically. For example, for regular measures, it would be someone who bought at least once a month for the last 6 months, but in specific months. For May, it would be the December-May range, for example. For April, it would shift one month to the left on the axis, etc.

And as I mentioned, I want to have two elements:

1. A single KPI, defaulting to the current month, but after selecting it for each range (selecting it truncates the model).

2. A line chart for the last 12 months – this could also be a static chart. 

I can try an As-Of table, but performance would drop dramatically on a large dataset. I'm looking for a way to calculate it quickly and efficiently, if possible.